De tempos em tempos, um desenvolvedor tem uma necessidade passar um conjunto de parâmetros ou mesmo uma seleção completa para a requisição "Na entrada." Às vezes, você se depara com soluções muito estranhas para esse problema.
Vamos abordar a questão "pelo lado oposto" e analisar o que não deve ser feito, porquê e como pode ser feito melhor.
Inserção direta de valores no corpo da requisição
Geralmente se parece com algo assim:
query = "SELECT * FROM tbl WHERE id = " + value…ou assim:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Este método já foi mencionado, descrito e bastante:
Quase sempre é - um caminho direto para injeções de SQL e uma carga extra na lógica de negócios, que é forçada a "colar" a sequência da sua consulta.
Essa abordagem só pode ser parcialmente justificada se necessário. uso de particionamento Nas versões do PostgreSQL 10 e anteriores, essa opção é usada para gerar um plano mais eficiente. Nessas versões, a lista de partições a serem verificadas é determinada sem levar em consideração os parâmetros passados, com base apenas no corpo da consulta.
$n-argumentos
Usar Os parâmetros são bons, permitem que você use , reduzindo a carga tanto na lógica de negócios (a string de consulta é gerada e transmitida apenas uma vez) quanto no servidor de banco de dados (não é necessário reanalisar e planejar para cada instância de consulta).
Número variável de argumentos
Os problemas surgirão quando quisermos passar um número desconhecido de argumentos:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Embora nos proteja de possíveis injeções, manter a consulta neste formato ainda exigirá a fusão/análise da consulta. para cada opção dentre o número de argumentosÉ melhor do que fazer isso sempre, mas você pode passar sem.
Basta passar apenas um parâmetro contendo representação serializada de um array:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'A única diferença é a necessidade de converter explicitamente o argumento para o tipo de array necessário. Mas isso não é um problema, já que já sabemos para onde estamos endereçando.
Transferência de uma amostra (matriz)
Normalmente, essas são diversas opções para transferir conjuntos de dados para inserção no banco de dados "em uma única solicitação":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Além dos problemas descritos acima com a "recolagem" da solicitação, isso também pode nos levar a fora da memória e uma falha no servidor. O motivo é simples: o PostgreSQL reserva memória adicional para argumentos, e o número de registros em um conjunto é limitado apenas pelos requisitos da lógica de negócios. Em casos clínicos específicos, eu vi Discussões "numeradas" sobre US$ 9000 — Não faça isso.
Vamos reescrever a consulta, aplicando-a já. serialização de "dois níveis":
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;
Sim, no caso de valores "complexos" dentro de uma matriz, eles precisam ser colocados entre aspas.
É evidente que, dessa forma, é possível "expandir" a amostra com um número arbitrário de campos.
desenterrar, desenterrar, …
Ocasionalmente, existem opções para passar várias "matrizes de colunas" em vez da "matriz de matrizes" que mencionei. :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Com esse método, se você cometer um erro ao gerar listas de valores para diferentes colunas, é muito fácil corrigi-lo completamente. resultados inesperados, que também dependem da versão do servidor:
-- $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
A partir da versão 9.3, o PostgreSQL passou a contar com funções completas para trabalhar com o tipo JSON. Portanto, se você definir parâmetros de entrada no navegador, poderá gerá-los diretamente ali. Objeto JSON para consulta SQL:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Para versões anteriores, o mesmo método pode ser usado para cada(hstore), mas o "dobramento" adequado com escape de objetos complexos no hstore pode causar problemas.
json_populate_recordset
Se você souber antecipadamente que os dados da matriz JSON de "entrada" serão usados para preencher uma tabela, poderá economizar significativamente na "desreferenciação" de campos e na conversão para os tipos necessários usando a função 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_para_conjunto_de_registros
E essa função simplesmente “desdobrará” a matriz de objetos passada em uma seleção, sem depender do formato da tabela:
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 | 2TABELA TEMPORÁRIA
Mas se o volume de dados na amostra transmitida for muito grande, então inseri-lo em um único parâmetro serializado é difícil, e às vezes impossível, já que requer uma operação única. alocações de memória grandesPor exemplo, imagine que você precisa coletar um grande lote de dados de eventos de um sistema externo durante um longo período e, em seguida, deseja processá-los uma única vez no banco de dados.
Nesse caso, a melhor solução seria usar :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
O método é bom, precisamente. para transmissão rara de grandes volumes dados.
Do ponto de vista da descrição da estrutura dos seus dados, uma tabela temporária difere de uma tabela “regular” apenas de uma maneira. na tabela de sistema pg_classe em pg_type, pg_depend, pg_attribute, pg_attrdef, … - Absolutamente nada.
Portanto, em sistemas web com um grande número de conexões de curta duração, tal tabela gerará novos registros de sistema para cada uma delas a cada vez, os quais serão excluídos quando a conexão com o banco de dados for fechada. Como resultado, O uso descontrolado de tabelas temporárias leva ao "inchaço" das tabelas no diretório pg_catalog. e atrasando muitas operações que os utilizam.
É claro que isso pode ser combatido com a ajuda de passagem periódica de VÁCUO COMPLETO de acordo com as tabelas do catálogo do sistema.
Variáveis de sessão
Vamos supor que o processamento de dados do caso anterior seja complexo o suficiente para uma única consulta SQL, mas queremos realizá-lo com frequência. Ou seja, queremos usar processamento procedural em Mas usar a transferência de dados por meio de tabelas temporárias será muito caro.
Também não podemos usar parâmetros `$n` para passar para um bloco anônimo. Variáveis de sessão e a função configuração_atual.
Antes da versão 9.2, era necessário pré-configurar. classes_de_variáveis_personalizadas para as variáveis de sessão "suas". Nas versões atuais, você pode escrever algo como:
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 : 3Outras soluções podem ser encontradas em outras linguagens procedimentais suportadas.
Conhece outras maneiras? Compartilhe nos comentários!
Fonte: habr.com
