sql - recorrer - Pasar una matriz de un tipo compuesto al procedimiento almacenado

procedimiento almacenado sql server ejemplo

Question

Probablemente estoy haciendo algo mal con la formación del literal. Supongamos que tengo un procedimiento almacenado simple como este:

CREATE OR REPLACE FUNCTION do_something(input_array composite_type[])
RETURNS SETOF text AS
$BODY$
DECLARE
temp_var composite_type;
BEGIN
FOR temp_var IN SELECT unnest(input_array) LOOP
return next temp_var.message;
END LOOP;
END
$BODY$
LANGUAGE plpgsql;

El composite_type se define como:

CREATE TYPE composite_type AS
(message text,
amount numeric(16,2));

Realizando una consulta como esta:

SELECT * FROM do_something(''{"(test,11)","(test2,22)"}'')

Produce este conjunto de resultados:

(test,11.00)
(test2,22.00)

En lugar de:

test
test2

¿Hay algo mal con mi literal o debería acceder al campo de message de una manera diferente? Gracias por cualquier sugerencia.

Answer 1

La forma en que especifique su entrada parece estar bien, ya que se observa el mismo comportamiento con la sintaxis del constructor de filas y matrices:

SELECT * FROM do_something( ARRAY[ ROW(''test'',11), ROW(''test2'',22) ]::composite_type[] );

Y:

SELECT ARRAY[ ROW(''test'',11), ROW(''test2'',22) ]::composite_type[];

produce:

 ''{"(test,11.00)","(test2,22.00)"}''

Si agrega un:

 RAISE NOTICE ''!%!'',temp_var;

dentro del ciclo la salida es:

NOTICE: !("(test,11.00)",)!
NOTICE: !("(test2,22.00)",)!

mostrando que en realidad está obteniendo una tupla con "mensaje" como el texto en tupla que esperaba y una "cantidad" nula.

Asi que. ¿Por qué?

Es un poco sutil. Estás usando:

SELECT unnest(input_array)

que parece hacer lo que quieres, ¿verdad?

regress=> SELECT unnest( ARRAY[ ROW(''test'',11), ROW(''test2'',22) ]::composite_type[] );
unnest
---------------
(test,11.00)
(test2,22.00)
(2 rows)

... pero en realidad, está devolviendo una sola columna del tipo composite_type . La asignación de tipo compuesto PL / PgSQL espera una columna por columna de tipo en su lugar. Entonces, el coll individual se está metiendo en ''mensaje'' y no hay un segundo col.

En cambio, escribe:

SELECT * FROM unnest(input_array)

para desempaquetar el compuesto para la asignación. Entonces funciona como se esperaba:

regress=> SELECT * FROM do_something( ARRAY[ ROW(''test'',11), ROW(''test2'',22) ]::composite_type[] );
do_something
--------------
test
test2
(2 rows)

Si el primer campo de composite_type fuera de tipo no textual, obtendría un error que era bastante más informativo sobre esto.

Answer 2

Craig explicó bien una razón para este comportamiento: la variable de asignación = valor dentro de la instrucción FORDE espera cero anidamiento. Entonces deberías hacer:

CREATE OR REPLACE FUNCTION do_something(input_array composite_type[])
RETURNS SETOF text AS $BODY$
DECLARE
temp_var record;
BEGIN
-- unnesting
FOR temp_var IN SELECT (unnest(input_array)).*
LOOP
RETURN NEXT temp_var.message;
END LOOP;
RETURN;
END
$BODY$ LANGUAGE plpgsql;

o - preferible - uso más reciente SetReturnedFunction dentro de "lista de columnas"

CREATE OR REPLACE FUNCTION do_something(input_array composite_type[])
RETURNS SETOF text AS $BODY$
DECLARE
temp_var record;
BEGIN
-- SELECT FROM
FOR temp_var IN SELECT * FROM unnest(input_array)
LOOP
RETURN NEXT temp_var.message;
END LOOP;
RETURN;
END
$BODY$ LANGUAGE plpgsql;