Passing an Array of Composite Type to a Stored Procedure
I am probably doing something wrong with literal shaping. Suppose I have a simple stored procedure:
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;
composite_type
defined as:
CREATE TYPE composite_type AS
(message text,
amount numeric(16,2));
Making a request like this:
SELECT * FROM do_something('{"(test,11)","(test2,22)"}')
Produces this result set:
(test,11.00)
(test2,22.00)
Instead:
test
test2
Is there something wrong with my literal or do I need to access the field message
differently? Thanks for any suggestions.
source to share
As you point out, your input appears to be accurate, as the same behavior occurs with the row-and-array-constructor syntax:
SELECT * FROM do_something( ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[] );
and
SELECT ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[];
gives:
'{"(test,11.00)","(test2,22.00)"}'
If you add:
RAISE NOTICE '!%!',temp_var;
inside the loop the output is:
NOTICE: !("(test,11.00)",)!
NOTICE: !("(test2,22.00)",)!
showing that you are actually getting a "message" tuple as the text of the tuple you expected and zero "sum".
So. Why?
It's a little thin. You're using:
SELECT unnest(input_array)
which seems to do what you want, correctly:
regress=> SELECT unnest( ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[] );
unnest
---------------
(test,11.00)
(test2,22.00)
(2 rows)
... but it actually returns a single column of type composite_type
. Composite Assignment PL / PgSQL assigns one column instead of a column. Thus, the only column is locked into the "message" and there is no second column.
Instead, write:
SELECT * FROM unnest(input_array)
to unpack the composite for destination. Then it works as expected:
regress=> SELECT * FROM do_something( ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[] );
do_something
--------------
test
test2
(2 rows)
If the first field composite_type
is of non-text type, you will get an error message that would be more informative.
source to share
Craig explained well the reason for this behavior - Assignment variable = value inside a FOR statement expects a zero nesting. So, you need to do:
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;
or - preferably - a new use of SetReturnedFunction inside a "list of columns"
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;
source to share