🥇PostgreSQL Antipatterns: Praeteriens Sets et Selections ad SQL

A interdum, elit eget, parametrorum statuto transire vel petitionem integram electionem "in introitu". Aliquando valde mirum est solutiones huius problematis.

Eamus "ab opposito" et vide quomodo non facias, quare et quomodo melius facere potes.

Direct "insertio" valorum in corpore rogationis

Solet spectat aliquid simile hoc;

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

... vel sic:

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

De hac methodo dicitur, scribitur et et instructa satis;

Fere semper est directum iter ad SQL iniectio et extra onus in logica negotio, quod "glutine" interrogationis tuae chorda cogitur.

Accessus hic ex parte iustificari potest, si opus sit. uti partitionibus in PostgreSQL versionibus 10 et infra ad consilium efficacius. In his versionibus, index sectionum lustratarum determinatur neglecto parametrorum transmissorum habita ratione corporis requisiti.

$ N argumentis

usum placeholders parametri bonus est, te uti sinit PARATUS STATEMENTAreducendo sarcinam tum ad rem logicam (quaestio chorda semel tantum formata et tradita est) tum in servo datorum (re-parsing et ratio non requiritur ad singulas instantiam interrogationis).

Variabilis numerus argumentorum

Problemata nos exspectant, cum ignotas praemissis argumentis transire volumus;

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

Si petitionem in hac forma exeatis, tum, licet nos a potentia injectionibus liberabit, tamen ad gluten / parse necessitatem ducet. pro unaquaque optione ex numero argumentorum. Melius est iam omni tempore facere, sed carere potes.

Sufficit ad unum modulum transire serialized repraesentatio ordinata:

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

Sola differentia est argumentum explicite convertendi ad desideratum genus ordinata. Sed hoc non causat problemata, quia iam praecogniti sumus ubi loquimur.

Sample translatio (matrix)

Solent haec omnia genera optionum pro transferendis notitias ponit inserendas datorum "in una petitione":

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

Praeter problemata supra scripta cum "re-gluu" petitionis, hoc etiam nos ducere potest de memoria et serv ruina. Ratio simplex - PG memoriam additis subsidiis pro argumentis, et numerus monumentorum in statuto limitatur solum negotio logicae applicationis Wishlist. Praesertim in casibus clinicis necessarium erat videre "Numerentur" rationes majores quam $ 9000 — Ne id facias.

Rescribe interrogationem, adhibitis iam "Duo-gradu" serialization:

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;

Ita, si valores "complexi" intra aciem ordinati sunt, opus est cum virgulis componi.
Patet hoc modo electionem "expandi" posse cum arbitrario agrorum numero.

unnest, unnest, ...

Interdum exstant optiones pro transeundo loco "ordinatae vestium" aliquot "columnarum", de quibus dixi. in ultimo articulo:

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

Hac ratione, si erras cum varias columnas valores generare, perfacile est perfecte acquirere. inopinatum eventusquae etiam in versione servo pendent;

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

Incipiens a versione 9.3, PostgreSQL functiones plenae-flexas habet operandi cum typo json. Si igitur parametri tui initus in navigatro definiuntur, ibi recte et formare potes json objectum SQL query:

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

Pro versionibus superioribus idem modus adhiberi potest quisque (hstore)sed recte "plicatio" cum elapsus complexorum in hstore difficultates causare potest.

json_populate_recordset

Si scias in antecessum notitias a "input" json ordinatas ibit ad implendum in aliqua mensa, multum in agris "dereferencing" servare potes et ad rationes requisitas mittendas utens munus 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

Et hoc munus simpliciter "expandi" transivit rerum ordinatarum in delectu, sine forma tabulae fretus:

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

TEMPORARIUS TABULA

Sed si copia notitiarum in sample transmissarum permagna est, eam in unum parametri serialisatum iacere difficile est, interdum impossibilis, cum requirit unum tempus. magna memoria destinatio. Exempli gratia, magnas eventus notitias ex systemate externo longo tempore colligere debes, et tunc vis unum tempus in parte datorum procedere.

In hoc casu optima solutio uti debet tempus tabulae:

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

Modus bonus raro transmissio magnarum voluminum data.
Mensa temporalis a mensa "regulari" in uno tantum pluma differt, secundum quod structuram notitiarum suarum describit. in pg_class ratio mensaet in pg_type, pg_depend, pg_attribute, pg_attrdef, ... — et nihil omnino.

Ideo in systematis interretialibus cum magna brevium temporum nexus pro unoquoque eorum, talis tabula novas tabulas systematis singulis diebus generabit, quae deleta sunt cum nexus datorum clauditur. Tandem, immoderatus usus Tabularum TEMPORUM ducit ad "tumorem" tabularum in pg_catalog et tarditatem multae operationes utentes.
Utique hoc potest pugnare cum transitum periodicum VACUUM FULL iuxta tabulas systematis catalogi.

Sessio Variabiles

Si processus notitiarum e casu praecedenti facta est pro uno SQL interrogatione satis implicata, sed hoc saepius facere vis. Hoc est, processui processui uti volumus DO obstructionumat usura notitia translationis per mensas temporarias nimis carus erit.

Etiam $n parametris uti non possumus ad scandalum anonymum transeundum. Sessionis variabiles et munus nos adiuvabit ex situ exire. current_setting.

Ante versionem 9.2, te praefigurare debebas speciale spatio nominali custom_variable_classes pro "sessione" variabilium. In emendationibus nostris, aliquid simile hoc scribere potes;

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

Aliae solutiones praesto sunt in aliis linguis processualibus fultis.

Plures scis? Share in comment!

Source: www.habr.com

PostgreSQL Antipatterns: Transit Sets et Eligit ad SQL