S vremena na vrijeme, programer treba proslijedite skup parametara ili čak cijeli odabir zahtjevu "na ulazu". Ponekad postoje vrlo čudna rješenja za ovaj problem.
Krenimo "od suprotnog" i vidimo kako to ne činiti, zašto i kako to možete učiniti bolje.
Izravno "umetanje" vrijednosti u tijelo zahtjeva
Obično izgleda ovako:
query = "SELECT * FROM tbl WHERE id = " + value... ili ovako:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)O ovoj metodi je rečeno, napisano i dovoljno:
Gotovo uvijek jest izravan put do SQL injekcije i dodatno opterećenje poslovne logike, koja je prisiljena "zalijepiti" vaš niz upita.
Ovaj pristup može biti djelomično opravdan samo ako je to potrebno. koristiti particioniranje u PostgreSQL verzijama 10 i nižim za učinkovitiji plan. U tim se verzijama popis skeniranih odjeljaka utvrđuje bez uzimanja u obzir prenesenih parametara, samo na temelju tijela zahtjeva.
$n argumenata
Koristiti parametri su dobri, omogućuju vam korištenje , smanjujući opterećenje na poslovnoj logici (niz upita formira se i prenosi samo jednom) i na poslužitelju baze podataka (ponovno analiziranje i planiranje nije potrebno za svaku instancu zahtjeva).
Promjenjivi broj argumenata
Problemi će nas čekati kada želimo proslijediti unaprijed nepoznat broj argumenata:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Ako ostavite zahtjev u ovom obliku, iako će nas to spasiti od potencijalnih injekcija, i dalje će dovesti do potrebe za lijepljenjem/raščlanjivanjem zahtjeva za svaku opciju od broja argumenata. Već bolje nego svaki put, ali možete i bez toga.
Dovoljno je proslijediti samo jedan parametar koji sadrži serijalizirani prikaz niza:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Jedina razlika je potreba eksplicitnog pretvaranja argumenta u željeni tip polja. Ali to ne stvara probleme, jer već unaprijed znamo gdje se obraćamo.
Prijenos uzorka (matrica)
Obično su to sve vrste opcija za prijenos skupova podataka za umetanje u bazu podataka "u jednom zahtjevu":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Osim gore opisanih problema s "prelijepljenjem" zahtjeva, to nas također može dovesti do bez memorije i pad poslužitelja. Razlog je jednostavan - PG rezervira dodatnu memoriju za argumente, a broj unosa u skupu ograničen je samo Wishlist aplikacije poslovne logike. U posebno kliničkim slučajevima bilo je potrebno vidjeti "numerirani" argumenti veći od 9000 USD - nemojte to raditi na ovaj način.
Prepišimo upit, već se prijavljujemo "dvorazinska" serijalizacija:
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;
Da, u slučaju "složenih" vrijednosti unutar niza, one moraju biti uokvirene navodnicima.
Jasno je da na ovaj način možete "proširiti" izbor s proizvoljnim brojem polja.
negnijezdo, negnijezdo, …
S vremena na vrijeme postoje opcije za prosljeđivanje umjesto "niza nizova" nekoliko "nizova stupaca" koje sam spomenuo :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Ovom metodom, ako pogriješite prilikom generiranja popisa vrijednosti za različite stupce, vrlo je lako u potpunosti dobiti neočekivani rezultati, koji također ovise o verziji poslužitelja:
-- $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
Počevši od verzije 9.3, PostgreSQL ima potpune funkcije za rad s tipom json. Stoga, ako su vaši ulazni parametri definirani u pregledniku, možete tamo i formirati json objekt za SQL upit:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Za prethodne verzije može se koristiti ista metoda svaki (hstore), ali ispravno "savijanje" s izbjegavanjem složenih objekata u hstoreu može uzrokovati probleme.
json_populate_recordset
Ako unaprijed znate da će podaci iz "ulaznog" json niza ići za popunjavanje neke tablice, možete puno uštedjeti na "dereferenciranju" polja i kastingu na tražene tipove pomoću funkcije 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
A ova funkcija će jednostavno "proširiti" proslijeđeni niz objekata u odabir, bez oslanjanja na format tablice:
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 | 2PRIVREMENI STOL
Ali ako je količina podataka u prenesenom uzorku vrlo velika, onda je njihovo prebacivanje u jedan serijalizirani parametar teško, a ponekad i nemoguće, budući da zahtijeva jednokratni velika raspodjela memorije. Na primjer, trebate prikupiti veliku količinu podataka o događajima iz vanjskog sustava dugo, dugo vremena, a zatim ih želite obraditi jednokratno na strani baze podataka.
U ovom slučaju, najbolje rješenje bilo bi koristiti :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
Metoda je dobra za rijedak prijenos velikih količina podaci.
Sa stajališta opisa strukture svojih podataka, privremena tablica razlikuje se od "obične" tablice samo po jednoj značajki. u tablici sustava pg_class, i u pg_type, pg_depend, pg_attribute, pg_attrdef, ... — i baš ništa.
Stoga će u web sustavima s velikim brojem kratkotrajnih veza za svaku od njih takva tablica svaki put generirati nove sistemske zapise koji se brišu kada se veza s bazom zatvori. Eventualno, nekontrolirano korištenje TEMP TABLE dovodi do "bubrenja" tablica u pg_cataloguu i usporavanje mnogih operacija koje ih koriste.
Naravno, protiv toga se može boriti periodični prolaz VAKUUM PUN prema tablicama kataloga sustava.
Varijable sesije
Pretpostavimo da je obrada podataka iz prethodnog slučaja prilično složena za jedan SQL upit, ali to želite raditi dosta često. Odnosno, želimo koristiti proceduralnu obradu u , ali korištenje prijenosa podataka kroz privremene tablice bit će preskupo.
Također ne možemo koristiti $n-parametre za prelazak na anonimni blok. Varijable sesije i funkcija pomoći će nam da izađemo iz situacije. trenutna_postavka.
Prije verzije 9.2 morali ste unaprijed konfigurirati prilagođene_varijable_klase za "njihove" varijable sesije. Na trenutnim verzijama možete napisati nešto poput ovoga:
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 : 3Postoje i druga rješenja dostupna u drugim podržanim proceduralnim jezicima.
Znate više načina? Podijelite u komentarima!
Izvor: www.habr.com