PostgreSQL հակապատկերներ. SQL-ին անցնելու սահմանում և ընտրություն

Ժամանակ առ ժամանակ մշակողը կարիք ունի փոխանցեք մի շարք պարամետրեր կամ նույնիսկ մի ամբողջ ընտրություն հարցումին «մուտքի մոտ». Երբեմն այս խնդրին շատ տարօրինակ լուծումներ կան։

Եկեք գնանք «հակառակից» և տեսնենք, թե ինչպես դա չանել, ինչու և ինչպես կարող եք դա անել ավելի լավ:

Արժեքների ուղղակի «ներդրում» հարցումի մարմնում

Այն սովորաբար նման է հետևյալին.

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

... կամ այսպես.

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

Այս մեթոդի մասին ասված է, գրված և նույնիսկ նկարված բավական:

Գրեթե միշտ այդպես է ուղիղ ճանապարհ դեպի SQL ներարկում և լրացուցիչ բեռ բիզնեսի տրամաբանության վրա, որը ստիպված է «սոսնձել» ձեր հարցման տողը:

Այս մոտեցումը կարող է մասնակիորեն արդարացվել միայն անհրաժեշտության դեպքում: օգտագործեք բաժանումը PostgreSQL 10 և ստորև տարբերակներում ավելի արդյունավետ պլանի համար: Այս տարբերակներում սկանավորված հատվածների ցանկը որոշվում է առանց փոխանցվող պարամետրերը հաշվի առնելու՝ միայն հարցման մարմնի հիման վրա։

$n փաստարկներ

Օգտագործում տեղապահներ պարամետրերը լավն են, այն թույլ է տալիս օգտագործել ՊԱՏՐԱՍՏՎԱԾ ՀԱՅՏԱՐԱՐՈՒԹՅՈՒՆՆԵՐ, նվազեցնելով բեռը և՛ բիզնես տրամաբանության վրա (հարցման տողը ձևավորվում և փոխանցվում է միայն մեկ անգամ), և՛ տվյալների բազայի սերվերի վրա (հարցման յուրաքանչյուր օրինակի համար կրկնակի վերլուծություն և պլանավորում չի պահանջվում):

Փաստարկների փոփոխական քանակ

Մեզ խնդիրներ կսպասեն, երբ մենք ցանկանանք նախապես փոխանցել անհայտ թվով փաստարկներ.

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

Եթե ​​հարցումը թողնեք այս ձևով, ապա թեև դա մեզ կփրկի հնարավոր ներարկումներից, այն դեռ կհանգեցնի հարցումը սոսնձելու/վերլուծելու անհրաժեշտությանը։ յուրաքանչյուր տարբերակի համար՝ փաստարկների քանակից. Արդեն ավելի լավ է, քան դա անել ամեն անգամ, բայց դուք կարող եք անել առանց դրա:

Բավական է փոխանցել միայն մեկ պարամետր, որը պարունակում է զանգվածի սերիական ներկայացում:

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

Միակ տարբերությունն այն է, որ անհրաժեշտ է բացահայտորեն փոխարկել արգումենտը ցանկալի զանգվածի տեսակին: Բայց դա խնդիրներ չի առաջացնում, քանի որ մենք արդեն նախապես գիտենք, թե ուր ենք դիմում։

Նմուշի փոխանցում (մատրիցան)

Սովորաբար սրանք բոլոր տեսակի տարբերակներ են տվյալների հավաքածուները «մեկ հարցումով» տվյալների բազա մուտքագրելու համար.

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

Ի հավելումն վերը նկարագրված խնդրանքի «վերասոսնձման» հետ կապված խնդիրներին, սա կարող է նաև մեզ տանել հիշողությունից դուրս և սերվերի խափանում: Պատճառը պարզ է՝ PG-ն արգումենտների համար լրացուցիչ հիշողություն է պահում, և հավաքածուի գրառումների քանակը սահմանափակվում է միայն բիզնես տրամաբանական հավելվածի Wishlist-ով: Հատկապես կլինիկական դեպքերում անհրաժեշտ էր տեսնել $9000-ից ավելի «համարակալված» փաստարկներ - մի արեք այսպես.

Եկեք վերաշարադրենք հարցումը՝ արդեն դիմելով «երկաստիճան» սերիականացում:

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;

Այո, զանգվածի ներսում «բարդ» արժեքների դեպքում դրանք պետք է շրջանակվեն չակերտներով:
Հասկանալի է, որ այս կերպ կարելի է «ընդլայնել» ընտրությունը կամայական թվով դաշտերով։

անբնույթ, անսարք,…

Ժամանակ առ ժամանակ կան տարբերակներ՝ «զանգվածների զանգվածի» փոխարեն փոխանցելու իմ նշած մի քանի «սյունակների զանգված». վերջին հոդվածում:

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

Այս մեթոդով, եթե սխալ եք թույլ տալիս տարբեր սյունակների համար արժեքների ցուցակներ ստեղծելիս, շատ հեշտ է ամբողջությամբ ստանալ անսպասելի արդյունքներ, որոնք նույնպես կախված են սերվերի տարբերակից.

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

Սկսած 9.3 տարբերակից՝ PostgreSQL-ն ունի json տիպի հետ աշխատելու լիարժեք գործառույթներ։ Հետևաբար, եթե ձեր մուտքագրման պարամետրերը սահմանված են բրաուզերում, կարող եք հենց այնտեղ և ձևավորել json օբյեկտ SQL հարցման համար:

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

Նախորդ տարբերակների համար նույն մեթոդը կարող է օգտագործվել յուրաքանչյուրը (hstore), բայց hstore-ում բարդ օբյեկտներից փախչող ճիշտ «ծալումը» կարող է խնդիրներ առաջացնել։

json_populate_recordset

Եթե ​​նախօրոք գիտեք, որ «մուտքագրված» json զանգվածի տվյալները կգնան լրացնելու ինչ-որ աղյուսակ, կարող եք շատ բան խնայել «անջատելու» դաշտերում և փոխանցել ցանկալի տեսակներին՝ օգտագործելով 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

Եվ այս ֆունկցիան պարզապես «կընդլայնի» անցած օբյեկտների զանգվածը ընտրության մեջ՝ առանց հենվելու աղյուսակի ձևաչափի վրա.

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

ԺԱՄԱՆԱԿԱՎՈՐ ՍԵՂԱՆ

Բայց եթե փոխանցված նմուշում տվյալների քանակը շատ մեծ է, ապա դրանք մեկ սերիական պարամետրի մեջ գցելը դժվար է, և երբեմն անհնար է, քանի որ դա պահանջում է մեկանգամյա հիշողության մեծ տեղաբաշխում. Օրինակ, դուք պետք է երկար, երկար ժամանակ արտաքին համակարգից հավաքեք իրադարձությունների տվյալների մեծ խմբաքանակ, այնուհետև ցանկանում եք դրանք մեկ անգամ մշակել տվյալների բազայի կողմից:

Այս դեպքում լավագույն լուծումը կլինի օգտագործելը ժամանակավոր սեղաններ:

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

Մեթոդը լավն է մեծ ծավալների հազվադեպ փոխանցման համար տվյալները։
Իր տվյալների կառուցվածքը նկարագրելու տեսանկյունից ժամանակավոր աղյուսակը «սովորական» աղյուսակից տարբերվում է միայն մեկ հատկանիշով. pg_class համակարգի աղյուսակում, իսկ ներսում pg_type, pg_կախված, pg_attribute, pg_attrdef, ... - և ընդհանրապես ոչինչ:

Հետևաբար, վեբ համակարգերում, որոնցից յուրաքանչյուրի համար մեծ թվով կարճատև կապեր կան, նման աղյուսակը ամեն անգամ կստեղծի նոր համակարգի գրառումներ, որոնք ջնջվում են տվյալների բազայի հետ կապը փակելու դեպքում: Ի վերջո, TEMP TABLE-ի անվերահսկելի օգտագործումը հանգեցնում է pg_catalog-ի աղյուսակների «ուռչացման» և դանդաղեցնելով դրանք օգտագործող բազմաթիվ գործողություններ:
Իհարկե, դրա հետ կարելի է պայքարել պարբերական անցում VACUUM FULL Համակարգի կատալոգի աղյուսակների համաձայն:

Նստաշրջանի փոփոխականներ

Ենթադրենք, որ նախորդ դեպքի տվյալների մշակումը բավականին բարդ է մեկ SQL հարցման համար, բայց դուք ցանկանում եք դա անել բավականին հաճախ: Այսինքն՝ մենք ցանկանում ենք օգտագործել ընթացակարգային մշակումը DO արգելափակել, սակայն ժամանակավոր աղյուսակների միջոցով տվյալների փոխանցման օգտագործումը չափազանց թանկ կլինի:

Մենք նաև չենք կարող օգտագործել $n-պարամետրեր՝ անանուն բլոկին անցնելու համար: Սեսիայի փոփոխականները և ֆունկցիան կօգնեն մեզ դուրս գալ իրավիճակից։ ընթացիկ_կարգավորում.

Մինչև 9.2 տարբերակը, դուք պետք է նախապես կազմաձևեիք հատուկ անվանատարածք custom_variable_classes «իրենց» նստաշրջանի փոփոխականների համար: Ընթացիկ տարբերակներում կարող եք գրել այսպիսի բան.

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

Կան այլ լուծումներ, որոնք հասանելի են այլ աջակցվող ընթացակարգային լեզուներով:

Գիտե՞ք ավելի շատ ուղիներ: Կիսվեք մեկնաբանություններում։

Source: www.habr.com