Linhas de retorno que correspondem a elementos da matriz de input na function plpgsql

Eu gostaria de criar uma function do PostgreSQL que faça algo como o seguinte:

CREATE FUNCTION avg_purchases( IN last_names text[] DEFAULT '{}' ) RETURNS TABLE(last_name text[], avg_purchase_size double precision) AS $BODY$ DECLARE qry text; BEGIN qry := 'SELECT last_name, AVG(purchase_size) FROM purchases WHERE last_name = ANY($1) GROUP BY last_name' RETURN QUERY EXECUTE qry USING last_names; END; $BODY$ 

Mas vejo dois problemas aqui:

  1. Não está claro para mim que o tipo de array é o tipo mais útil de input.
  2. Isso está retornando atualmente zero linhas quando eu faço:

     SELECT avg_purchases($${'Brown','Smith','Jones'}$$); 

o que estou perdendo?

Isso funciona:

 CREATE OR REPLACE FUNCTION avg_purchases(last_names text[] = '{}') RETURNS TABLE(last_name text, avg_purchase_size float8) AS $func$ SELECT last_name, AVG(purchase_size)::float8 FROM purchases WHERE last_name = ANY($1) GROUP BY last_name $func$ LANGUAGE sql; 

Ligar:

 SELECT * FROM avg_purchases('{foo,Bar,baz,"}weird_name''$$"}'); 

Ou (update – exemplo com cotação em dólar ):

 SELECT * FROM avg_purchases($x${foo,Bar,baz,"}weird_name'$$"}$x$); 
  • Mais sobre como citar literais de string:
    Inserir texto com aspas simples no PostgreSQL

  • Você não precisa de SQL dynamic aqui.

  • Enquanto você pode envolvê-lo em uma function plpgsql (que pode ser útil), uma simples function SQL está fazendo o trabalho muito bem.

  • Você tem incompatibilidades de tipo .

    • o resultado de avg() pode ser numeric para manter um resultado preciso. Eu lancei para float8 para fazer isso funcionar, o que é apenas um alias para double precision (você também pode usar). Se você precisa de precisão perfeita, use um valor numeric .
    • Desde que você GROUP BY last_name você quer um parâmetro OUT text simples em vez de text[] .

VARIADIC

Uma matriz é um tipo útil de input. Se for mais fácil para seu cliente, você também pode usar um parâmetro de input VARIADIC que permite transmitir a matriz como uma lista de elementos :

 CREATE OR REPLACE FUNCTION avg_purchases(VARIADIC last_names text[] = '{}') RETURNS TABLE(last_name text, avg_purchase_size float8) AS $func$ SELECT last_name, AVG(purchase_size)::float8 FROM purchases JOIN (SELECT unnest($1)) t(last_name) USING (last_name) GROUP BY last_name $func$ LANGUAGE sql 

Ligar:

 SELECT * FROM avg_purchases('foo', 'Bar', 'baz', '"}weird_name''$$"}'); 

Ou (com cotação em dólar):

 SELECT * FROM avg_purchases('foo', 'Bar', 'baz', $y$'"}weird_name'$$"}$y$); 

Esteja ciente de que o Postgres padrão permite apenas um máximo de 100 elementos . Isso é determinado em tempo de compilation pela opção predefinida :

max_function_args (integer)

Relata o número máximo de argumentos da function. É determinado pelo valor de FUNC_MAX_ARGS ao construir o servidor. O valor padrão é 100 argumentos.

Você ainda pode chamá-lo com a notação array quando prefixado com a palavra-chave VARIADIC :

 SELECT * FROM avg_purchases(VARIADIC '{1,2,3, ... 99,100,101}'); 

Para matrizes maiores (100+), eu também usaria unnest() em uma subconsulta e JOIN nela, que tende a escalar melhor:

  • Otimizando uma consulta do Postgres com um grande IN
Intereting Posts