Ti jaman ka jaman, pamekar perlu lulus susunan parameter atawa malah hiji sakabéh pilihan pikeun pamundut nu "dina lawang". Kadang-kadang aya solusi anu aneh pikeun masalah ieu.
Hayu urang balik "ti sabalikna" tur tingal kumaha teu ngalakukeun eta, naha, jeung kumaha anjeun tiasa ngalakukeun eta hadé.
Langsung "sisipan" nilai dina awak pamundut
Biasana katingali sapertos kieu:
query = "SELECT * FROM tbl WHERE id = " + value... atanapi sapertos kieu:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Ngeunaan metoda ieu ceuk, ditulis na cekap:
Ampir sok kitu jalur langsung ka SQL suntik sarta beban tambahan dina logika bisnis, nu kapaksa "lem" string pamundut anjeun.
Pendekatan ieu tiasa dibenerkeun sawaréh ngan upami diperyogikeun. ngagunakeun partitioning dina versi PostgreSQL 10 sareng handap pikeun rencana anu langkung éfisién. Dina versi ieu, daptar bagian discan ditangtukeun tanpa nyokot kana akun parameter dikirimkeun, ngan dina dasar badan pamundut.
$n argumen
pamakean parameter anu alus, eta ngidinan Anjeun pikeun make , ngurangan beban duanana dina logika bisnis (string query kabentuk sarta dikirimkeun ngan sakali) jeung dina server database (ulang parsing jeung tata henteu diperlukeun pikeun tiap conto pamundut teh).
Jumlah variabel argumen
Masalah bakal ngadagoan urang nalika urang hoyong lulus sajumlah argumen anu teu dipikanyaho sateuacanna:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Upami anjeun ngantunkeun pamundut dina bentuk ieu, sanaos éta bakal nyalametkeun urang tina poténsi suntikan, éta masih bakal nyababkeun kabutuhan pikeun lem / parse pamundut éta. pikeun tiap pilihan tina jumlah argumen. Geus hadé ti ngalakukeun eta unggal waktu, tapi anjeun bisa ngalakukeun tanpa eta.
Ieu cukup pikeun lulus ngan hiji parameter ngandung ngagambarkeun serialized tina hiji Asép Sunandar Sunarya:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Hiji-hijina bédana nyaéta kabutuhan sacara eksplisit ngarobih argumen kana jinis array anu dipikahoyong. Tapi ieu teu ngabalukarkeun masalah, sabab urang geus terang sateuacanna dimana urang alamat.
Mindahkeun sampel (matriks)
Biasana ieu mangrupikeun sagala jinis pilihan pikeun nransferkeun set data pikeun diselapkeun kana pangkalan data "dina hiji pamundut":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Salian masalah ditétélakeun di luhur kalawan "ulang gluing" pamundut, ieu ogé bisa ngakibatkeun urang kaluar tina ingetan jeung kacilakaan server. Alesanna basajan - PG cadangan mémori tambahan pikeun argumen, sareng jumlah rékaman dina set diwatesan ukur ku Wishlist aplikasi logika bisnis. Dina kasus klinis utamana diperlukeun pikeun nempo argumen "numbered" leuwih gede ti $9000 - ulah ngalakukeun cara kieu.
Hayu urang nulis ulang query, nerapkeun geus serialization "dua-tingkat".:
INSERT INTO tbl
SELECT
unnest[1]::text k
, unnest[2]::integer v
FROM (
SELECT
unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;
Leres, dina kasus nilai "kompleks" dina hiji array, aranjeunna kedah dipiguraan ku tanda petik.
Éta jelas yén ku cara ieu anjeun tiasa "ngalegaan" pilihan kalayan sajumlah lapangan anu sawenang.
unnes, unnes,…
Ti jaman ka jaman aya pilihan pikeun ngalirkeun tinimbang hiji "array of arrays" sababaraha "arrays of kolom" nu kuring disebutkeun. :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Kalayan padika ieu, upami anjeun ngalakukeun kasalahan nalika ngahasilkeun daptar nilai pikeun kolom anu béda-béda, gampang pisan pikeun lengkep. hasilna teu kaduga, anu ogé gumantung kana versi server:
-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |JSON
Mimitian ti vérsi 9.3, PostgreSQL ngagaduhan fungsi anu lengkep pikeun damel sareng jinis json. Ku alatan éta, upami parameter input anjeun ditetepkeun dina browser, anjeun tiasa langsung aya sareng ngabentuk json objek pikeun query SQL:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Pikeun vérsi saméméhna, cara anu sami tiasa dianggo pikeun masing-masing (hstore), Tapi bener "tilepan" kalawan escaping objék kompléks di hstore bisa ngabalukarkeun masalah.
json_populate_recordset
Upami anjeun terang sateuacanna yén data tina "input" json Asép Sunandar Sunarya bakal ngeusian sababaraha tabel, anjeun tiasa ngahemat pisan dina widang "dereferencing" sareng tuang kana jinis anu dipikahoyong nganggo fungsi json_populate_recordset:
SELECT
*
FROM
json_populate_recordset(
NULL::pg_class
, $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
);json_to_recordset
Sareng fungsi ieu ngan saukur "ngalegaan" asép Sunandar Sunarya objék kana pilihan, tanpa ngandelkeun format méja:
SELECT
*
FROM
json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2Méja samentara
Tapi lamun jumlah data dina sampel dikirimkeun pisan badag, lajeng ngalungkeun kana hiji parameter serialized hese, sarta kadangkala teu mungkin, sabab merlukeun hiji-waktos. alokasi memori badag. Contona, Anjeun kudu ngumpulkeun bets badag data acara ti sistem éksternal pikeun lila, lila, lajeng Anjeun hoyong ngolah hiji-waktos di sisi database.
Dina hal ieu, solusi pangalusna bakal ngagunakeun :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
Métodena saé pikeun pangiriman jarang tina volume badag data.
Ti sudut pandang ngajéntrékeun struktur data na, tabel samentara béda ti tabel "biasa" dina ngan hiji fitur. dina tabel sistem pg_class, sareng di pg_type, pg_depend, pg_attribute, pg_attrdef, ... - jeung nanaon pisan.
Ku alatan éta, dina sistem web kalawan angka nu gede ngarupakeun sambungan pondok-cicing pikeun tiap sahijina, tabel sapertos bakal ngahasilkeun rékaman sistem anyar unggal waktu, nu dihapus nalika sambungan ka database ditutup. antukna, pamakéan uncontrolled TEMP TABLE ngabalukarkeun "bareuh" tabel di pg_catalog sareng ngalambatkeun seueur operasi anu ngagunakeunana.
Tangtu, ieu bisa combated kalawan périodik pass VACUUM FULL nurutkeun tabel katalog sistem.
Variabel sési
Anggap ngolah data tina kasus saméméhna cukup kompleks pikeun query SQL tunggal, tapi anjeun hoyong ngalakukeun eta rada mindeng. Hartina, urang rék ngagunakeun processing prosedural di , tapi ngagunakeun mindahkeun data ngaliwatan tabel samentara bakal mahal teuing.
Urang ogé teu bisa maké $n-parameter pikeun lolos ka blok anonim. Variabel sési sareng fungsina bakal ngabantosan urang kaluar tina kaayaan éta. current_setting.
Sateuacan versi 9.2, anjeun kedah tos ngonpigurasikeun custom_variable_classes pikeun variabel sési "maranéhna". Dina versi ayeuna, anjeun tiasa nyerat sapertos kieu:
SET my.val = '{1,2,3}';
DO $$
DECLARE
id integer;
BEGIN
FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
RAISE NOTICE 'id : %', id;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE: id : 1
-- NOTICE: id : 2
-- NOTICE: id : 3Aya solusi sejenna sadia dina basa prosedural dirojong séjén.
Nyaho langkung seueur cara? Bagikeun dina komentar!
sumber: www.habr.com