postgresql - variable - if en funciones postgres

Argumento opcional en la función PL/pgSQL

Question

Estoy tratando de escribir una función PL / pgSQL con argumentos opcionales. Realiza una consulta basada en un conjunto de registros filtrados (si se especifica), de lo contrario realiza una consulta en todo el conjunto de datos en una tabla.

Por ejemplo (CÓDIGO PSEUDO) :

CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids=[]) RETURNS SETOF RECORD AS $$
IF len(optional_list_of_ids) > 0 THEN
RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2 AND id in optional_list_of_ids);
ELSE
RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2);
ENDIF
$$ LANGUAGE SQL;

¿Cuál sería la forma correcta de implementar esta función?

Por otro lado, me gustaría saber cómo podría llamar a tal función en otra función externa. Así es como lo haría, ¿es correcto o hay una mejor manera?

CREATE FUNCTION foofuncwrapper(param1 integer, param2 date, param2 date) RETURNS SETOF RECORD AS $$
BEGIN
CREATE TABLE ids AS SELECT id from foobar where id < 100;
RETURN QUERY (SELECT * FROM foofunc(param1, param2, ids));
END
$$ LANGUAGE SQL

Answer 1

¿Quiere decir funciones de SQL con números variables de argumentos ? Si es así, usa VARIADIC.

Answer 2

Desde PostgreSQL 8.4 (que parece que se está ejecutando), hay valores predeterminados para los parámetros de la función . Si pone su parámetro al final y proporciona un valor predeterminado, simplemente puede omitirlo de la llamada:

CREATE OR REPLACE FUNCTION foofunc(_param1 integer
, _param2 date
, _ids int[] DEFAULT ''{}'')
RETURNS SETOF foobar AS -- declare return type!
$func$
BEGIN -- required for plpgsql
IF _ids <> ''{}''::int[] THEN -- exclude empty array and NULL
RETURN QUERY
SELECT *
FROM foobar
WHERE f1 = _param1
AND f2 = _param2
AND id = ANY(_ids); -- "IN" is not proper syntax for arrays
ELSE
RETURN QUERY
SELECT *
FROM foobar
WHERE f1 = _param1
AND f2 = _param2;
END IF;
END -- required for plpgsql
$func$ LANGUAGE plpgsql;

Puntos principales:

• La palabra clave DEFAULT se utiliza para declarar los valores predeterminados de los parámetros. Alternativa corta: = .

• param1 el param1 redundante del desordenado ejemplo.

• Como devuelve SELECT * FROM foobar , declare el tipo de devolución como RETURNS SETOF foobar lugar de RETURNS SETOF record . La última forma con registros anónimos es muy difícil de manejar, debe proporcionar una lista de definición de columnas con cada llamada.

• Yo uso una matriz de enteros ( int[] ) como parámetro de función. Se adaptó la expresión IF y la cláusula WHERE consecuencia.

• IF declaraciones IF no están disponibles en SQL puro. Tiene que ser LANGUAGE plpgsql para eso.

Llamar con o sin _ids :

SELECT * FROM foofunc(1, ''2012-1-1''::date);

Efectivamente lo mismo

SELECT * FROM foofunc(1, ''2012-1-1''::date, ''{}''::int[]);

Debe asegurarse de que la llamada no sea ambigua. Si tiene otra función del mismo nombre y dos parámetros, es posible que Postgres no sepa cuál elegir. El lanzamiento explícito (como lo demuestro) lo reduce. De lo contrario, los literales de cadena sin tipo también funcionan, pero ser explícito nunca duele.

Llamada desde dentro de otra función:

CREATE FUNCTION foofuncwrapper(_param1 integer, _param2 date)
RETURNS SETOF foobar AS
$func$
DECLARE
_ids int[] := ''{1,2,3}'';
BEGIN
-- irrelevant stuff
RETURN QUERY
SELECT * FROM foofunc(_param1, _param2, _ids);
END
$func$ LANGUAGE plgpsql;

Answer 3

Elaborando la respuesta de Frank en este hilo:

El VARIADIC VARIADIC no tiene que ser el único argumento, solo el último.

Puede usar VARIADIC para funciones que pueden tener cero argumentos variados, es un poco más complejo ya que requiere un estilo de llamada diferente para cero argumentos. Puede proporcionar una función de envoltura para ocultar lo feo. Dada una definición de función varardica inicial como:

CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids VARIADIC integer[]) RETURNS SETOF RECORD AS $$
....
$$ language sql;

Para cero argumentos use un contenedor como:

CREATE OR REPLACE FUNCTION foofunc(integer, date, date) RETURNS SETOF RECORD AS $body$
SELECT foofunc($1,$2,$3,VARIADIC ARRAY[]::integer[]);
$body$ LANGUAGE ''sql'';

o simplemente llame al func principal con una matriz vacía como VARIADIC ''{}''::integer[] directamente. El envoltorio es feo, pero contiene fealdad, por lo que recomiendo usar un envoltorio.

Las llamadas directas se pueden hacer en forma variadica:

SELECT foofunc(1,''2011-01-01'',''2011-01-01'', 1, 2, 3, 4);

... o forma de llamada de matriz con array ctor:

SELECT foofunc(1,''2011-01-01'',''2011-01-01'', VARIADIC ARRAY[1,2,3,4]);

... o una forma literal de texto de matriz:

SELECT foofunc(1,''2011-01-01'',''2011-01-01'', VARIADIC ''{1,2,3,4}''::int[]);

Las últimas dos formas funcionan con matrices vacías.