ProHoster > Блог > Administrado > PostgreSQL-Antiŝablonoj: Transdono de Aroj kaj Elektoj al SQL

PostgreSQL-Antiŝablonoj: Transdono de Aroj kaj Elektoj al SQL

De tempo al tempo, la programisto bezonas transdonu aron da parametroj aŭ eĉ tutan elekton al la peto "ĉe la enirejo". Kelkfoje estas tre strangaj solvoj al ĉi tiu problemo.
PostgreSQL-Antiŝablonoj: Transdono de Aroj kaj Elektoj al SQL
Ni iru "de la malo" kaj vidu kiel ne fari ĝin, kial, kaj kiel vi povas fari ĝin pli bone.

Rekta "enigo" de valoroj en la petokorpon

Ĝi kutime aspektas kiel ĉi tio:

query = "SELECT * FROM tbl WHERE id = " + value

... aŭ tiel:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

Pri tiu ĉi metodo oni diras, skribas kaj eĉ desegnita sufiĉe:

PostgreSQL-Antiŝablonoj: Transdono de Aroj kaj Elektoj al SQL

Preskaŭ ĉiam estas rekta vojo al SQL-injekto kaj kroma ŝarĝo sur la komerca logiko, kiu estas devigita "glui" vian demandan ĉenon.

Ĉi tiu aliro povas esti parte pravigita nur se necese. uzi dispartigo en PostgreSQL-versioj 10 kaj sube por pli efika plano. En ĉi tiuj versioj, la listo de skanitaj sekcioj estas determinita sen konsideri la transdonitajn parametrojn, nur surbaze de la petokorpo.

$n argumentoj

Uzo anstataŭiloj parametroj estas bona, ĝi permesas vin uzi PREPARITAJ DEKLAROJ, reduktante la ŝarĝon kaj sur la komerca logiko (la demandŝnuro estas formita kaj elsendita nur unufoje) kaj sur la datumbaza servilo (re-analizado kaj planado ne estas postulataj por ĉiu kazo de la peto).

Varia nombro da argumentoj

Problemoj atendos nin kiam ni volas antaŭpasi nekonatan nombron da argumentoj:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Se vi lasas la peton en ĉi tiu formo, tiam kvankam ĝi savos nin de eblaj injektoj, ĝi ankoraŭ kondukos al la bezono glui / analizi la peton. por ĉiu opcio el la nombro da argumentoj. Jam pli bone ol fari ĝin ĉiufoje, sed vi povas malhavi ĝin.

Sufiĉas pasi nur unu parametron enhavantan seriigita reprezentado de tabelo:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

La nura diferenco estas la bezono eksplicite konverti la argumenton al la dezirata tabeltipo. Sed ĉi tio ne kaŭzas problemojn, ĉar ni jam anticipe scias kien ni traktas.

Specimena translokigo (matrico)

Kutime ĉi tiuj estas ĉiaj ebloj por translokigi datumojn por enmeti en la datumbazon "en unu peto":

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Krom la supre priskribitaj problemoj pri la "regluado" de la peto, ĉi tio ankaŭ povas konduki nin al el memoro kaj servilo kraŝo. La kialo estas simpla - PG rezervas plian memoron por la argumentoj, kaj la nombro da rekordoj en la aro estas limigita nur de la komerca logika aplikaĵo Wishlist. En precipe klinikaj kazoj necesis vidi "numeritaj" argumentoj pli grandaj ol $9000 - ne faru tion tiel.

Ni reverku la demandon, aplikante jam "dunivela" seriigo:

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;

Jes, en la kazo de "kompleksaj" valoroj ene de tabelo, ili devas esti enkadrigitaj per citaĵoj.
Estas klare, ke tiamaniere vi povas "vastigi" la elekton per arbitra nombro da kampoj.

malnestigi, malnestigi,...

De tempo al tempo estas ebloj por pasi anstataŭ "tabelo de tabeloj" plurajn "tabelojn de kolumnoj", kiujn mi menciis. en la lasta artikolo:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Kun ĉi tiu metodo, se vi eraras kiam vi generas listojn de valoroj por malsamaj kolumnoj, estas tre facile akiri tute neatenditaj rezultoj, kiuj ankaŭ dependas de la servila versio:

-- $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

Ekde la versio 9.3, PostgreSQL havas plenajn funkciojn por labori kun la json-tipo. Tial, se viaj eniga parametroj estas difinitaj en la retumilo, vi povas ĝuste tie kaj formi json objekto por SQL-demando:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Por antaŭaj versioj, la sama metodo povas esti uzata por ĉiu (hbutiko), sed ĝusta "faldado" kun eskapi kompleksaj objektoj en hstore povas kaŭzi problemojn.

json_populate_recordset

Se vi scias anticipe, ke la datumoj de la "enigo" json-tabelo iros por plenigi iun tabelon, vi povas ŝpari multe en "malreferenci" kampoj kaj ĵetado al la dezirataj tipoj uzante la json_populate_recordset funkcio:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

Kaj ĉi tiu funkcio simple "vastigos" la pasitan tabelon de objektoj en elekton, sen fidi je la tabelformato:

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 | 2

PROPOZA TABLO

Sed se la kvanto de datumoj en la elsendita specimeno estas tre granda, tiam ĵeti ĝin en unu seriigitan parametron estas malfacila, kaj foje neebla, ĉar ĝi postulas unufojan granda memora atribuo. Ekzemple, vi devas kolekti grandan aron da eventodatenoj de ekstera sistemo dum longa, longa tempo, kaj tiam vi volas prilabori ĝin unufoje ĉe la datumbazo.

En ĉi tiu kazo, la plej bona solvo estus uzi provizoraj tabloj:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

La metodo estas bona por malofta dissendo de grandaj volumoj datumoj.
De la vidpunkto de priskribado de la strukturo de ĝiaj datumoj, provizora tabelo diferencas de "regula" tabelo en nur unu trajto. en pg_class-sistema tabelokaj en pg_type, pg_depend, pg_attribute, pg_attrdef, ... — kaj tute nenio.

Sekve, en retaj sistemoj kun granda nombro da mallongdaŭraj konektoj por ĉiu el ili, tia tablo generos novajn sistemajn registrojn ĉiufoje, kiuj estas forigitaj kiam la konekto al la datumbazo estas fermita. Fine, nekontrolita uzo de TEMP TABLE kondukas al "ŝveliĝo" de tabeloj en pg_catalog kaj malrapidigi multajn operaciojn, kiuj uzas ilin.
Kompreneble, ĉi tio povas esti kontraŭbatalita perioda pase VACUUM PLENA laŭ la sistemaj katalogaj tabeloj.

Sesiaj Variabloj

Supozu, ke la prilaborado de la datumoj de la antaŭa kazo estas sufiĉe kompleksa por ununura SQL-demando, sed vi volas fari ĝin sufiĉe ofte. Tio estas, ni volas uzi proceduran prilaboradon en DO bloki, sed uzi datumtransdonon per provizoraj tabeloj estos tro multekosta.

Ni ankaŭ ne povas uzi $n-parametrojn por pasi al anonima bloko. La seancaj variabloj kaj la funkcio helpos nin eliri el la situacio. aktuala_agordo.

Antaŭ versio 9.2, vi devis antaŭ-agordi speciala nomspaco kutimaj_variablaj_klasoj por "iliaj" seancaj variabloj. En aktualaj versioj, vi povas skribi ion tian:

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 : 3

Estas aliaj solvoj haveblaj en aliaj subtenataj proceduraj lingvoj.

Ĉu vi scias pli da manieroj? Kunhavigu en la komentoj!

fonto: www.habr.com

Aldoni komenton