ืคืื ืฆืืื ืฆื ืฆืืื ืึท ืืขืืืขืืึธืคึผืขืจ ืืึทืจืฃ ืคืึธืจื ืึท ืกืืื ืคืื ืคึผืึทืจืึทืืขืืขืจืก ืึธืืขืจ ืืคืืื ืึท ืืึทื ืฅ ืกืขืืขืงืฆืืข ืฆื ืื ืืขืื "ืืืึท ืื ืึทืจืืึทื ืืึทื ื". ืืื ืืืจ ืงืืืขื ืึทืจืืืขืจ ืืืืขืจ ืืึธืื ืข ืกืึทืืืฉืึทื ื ืฆื ืืขื ืคึผืจืึธืืืขื.
ืืึธืืืจ ืืืื ืฆืืจืืง ืืื ืืขื ืืืึธืก ื ืื ืฆื ืืึธื, ืืืึธืก, ืืื ืืื ืืืจ ืงืขื ืขื ืืึธื ืืึธืก ืืขืกืขืจ.
ืืืจืขืงื ืื ืกืขืจืฉืึทื ืคืื ืืืึทืืืขืก ืืื ืื ืืขืื ืืืฃ
ืขืก ืืืืฉืึทืืืึทืื ืงืืงื ืขืคึผืขืก ืืื ืืึธืก:
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),...ืืื ืึทืืืฉืึทื ืฆื ืื ืคึผืจืึธืืืขืืก ืืืกืงืจืืืื ืืืืื ืืื "ืฉืืึทืขื-ืืืืื ื" ืื ืืงืฉื, ืืึธืก ืงืขื ืืืื ืคืืจื ืืื ืื ืฆื ืืืืก ืคืื ืืึผืจืื ืืื ืกืขืจืืืขืจ ืงืจืึทื. ืื ืกืืื ืืื ืคึผืฉืื - ืคึผื ืจืืืขืจืืื ื ืึธื ืืืงืึธืจื ืคึฟืึทืจ ืึทืจืืืืขื ืื, ืืื ืื ื ืืืขืจ ืคืื ืจืขืงืึธืจืืก ืืื ืืขื ืืึทื ื ืืื ืืืืืืขื ืืืืื ืืืจื ืื ืึทืคึผืืึทืงืืืฉืึทื ืืืืขืจืคืขื ืืฉื ืคืื ืื ืืขืฉืขืคื ืืึธืืืง. ืืื ืืขืจ ืืืืคึผื ืงืืื ืืฉ ืงืึทืกืขืก ืืื ืืขืืื ืฆื ืืขื "ื ืืืขืจ" ืึทืจืืืืขื ืื ืืขื ืขื ืืขืจ ืืื $ 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 |ืืืฉืกืึธื
ืืื ื ืืืขืจืกืืข 9.3, PostgreSQL ืืื ืคืื-ืคืืขืืืฉื ืคืึทื ืืงืฉืึทื ื ืคึฟืึทืจ ืืจืืขืื ืืื ืื json ืืืคึผ. ืืขืจืืืขืจ, ืืืื ืื ืืขืคึฟืื ืืฆืืข ืคืื โโืึทืจืืึทื ืฉืจืืึทื ืคึผืึทืจืึทืืขืืขืจืก ืึทืงืขืจื ืืื ืืืื ืืืขืืขืจืขืจ, โโืืืจ ืงืขื ืขื ืคืึธืจืขื ืขืก ืจืขืื ืืึธืจื json ืืืืคืขืฅ ืคึฟืึทืจ SQL ืึธื ืคึฟืจืขื:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'ืคึฟืึทืจ ืคืจืืึทืขืจืืืง ืืืขืจืกืืขืก, ืืขืจ ืืขืืืืงืขืจ ืืืคึฟื ืงืขื ืขื ืืืื ืืขืืืืื ื ืืขืืขืจ (ืืกืืึธืจ), ืึธืืขืจ ืจืืืืืง "ืงืึธื ืืืึทืืืฉืึทื" ืืื ืืกืงืืืคึผืื ื ืงืึธืืคึผืืขืงืก ืึทืืืืฉืขืงืฅ ืืื ืืกืืึธืจืข ืงืขื ืขื ืืจืื ื ืคึผืจืึธืืืขืืก.
json_populate_recordset
ืืืื ืืืจ ืืืืกื ืืื ืฉืืืึทืื ืึทื ืื ืืึทืื ืคืื ืื "ืื ืคึผืื" ืืืฉืกืึธื ืืขื ืืข ืืืขื ืืืื ืืขืืืืื ื ืฆื ืคึผืืึธืืืืจื ืขืืืขืืข ืืืฉ, ืืืจ ืงืขื ืขื ืจืึทืืขืืืขื ืึท ืคึผืืึทืฅ ืืื "ืืขืจืขืคืขื ืกืื ื" ืคืขืืืขืจ ืืื ืงืึทืกืืื ื ืืื ืฆื ืื ืคืืจืืื ืื ืืืืคึผืก ืืืจื ื ืืฆื ืื 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