🥇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),...

د غوښتنې د "بیا gluing" سره پورته تشریح شوي ستونزو سربیره، دا هم کولی شي موږ ته الر پیدا کړي له حافظې بهر او د سرور ټکر. دلیل ساده دی - PG د دلیلونو لپاره اضافي حافظه خوندي کوي، او په سیټ کې د ننوتلو شمیر یوازې د سوداګرۍ منطق غوښتنلیک مطلوب لیست لخوا محدود دی. په ځانګړي توګه په کلینیکي قضیو کې دا اړینه وه چې وګورئ "نمبر شوي" دلیلونه له $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 ډول سره کار کولو لپاره بشپړ فعالیتونه لري. له همدې امله، که ستاسو د ننوتلو پیرامیټونه په براوزر کې تعریف شوي وي، تاسو کولی شئ هلته سم او فورمه جوړه کړئ د SQL پوښتنې لپاره json اعتراض:

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

د پخوانیو نسخو لپاره، ورته طریقه کارول کیدی شي هر یو (د پلورنځي)، مګر په hstore کې د پیچلو شیانو د تیښتې سره سم "تړون" کولی شي ستونزې رامینځته کړي.

json_populate_recordset

که تاسو دمخه پوهیږئ چې د "input" 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_depend, pg_attribute, pg_attrdef, ... - او هیڅ شی نه.

له همدې امله، په ویب سیسټمونو کې چې د دوی د هر یو لپاره د لوی شمیر لنډمهاله اړیکو سره، دا ډول میز به هر ځل د سیسټم نوي ریکارډونه رامینځته کړي، کوم چې حذف کیږي کله چې ډیټابیس سره اړیکه وتړل شي. بالاخره، د TEMP TABLE غیر کنټرول کارول په pg_catalog کې د میزونو "پړسوب" لامل کیږي او ډیری عملیات ورو کوي چې دوی کاروي.
البته، د دې سره مبارزه کیدی شي دوراني پاس ویکیوم بشپړ د سیسټم کتلاګ جدولونو مطابق.

د سیشن تغیرات

فرض کړئ چې د تیرې قضیې څخه د معلوماتو پروسس کول د یو واحد SQL پوښتنې لپاره خورا پیچلي دي، مګر تاسو غواړئ دا ډیری وختونه ترسره کړئ. دا دی، موږ غواړو د طرزالعمل پروسس څخه کار واخلو بلاک کړئ، مګر د لنډمهاله میزونو له لارې د معلوماتو لیږد کارول به خورا ګران وي.

موږ هم نشو کولی $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

په نورو ملاتړ شوي طرزالعملونو کې نورې حل لارې شتون لري.

نورې لارې پېژنئ؟ په نظرونو کې شریک کړئ!

سرچینه: www.habr.com

PostgreSQL ضد نمونې: ایس کیو ایل ته د سیټونو او انتخابونو انتقال کول