Using custom return types in a FOR loop in plpgsql
The following code I am using returns an integer 1
:
CREATE TYPE my_test AS (
foo Integer
);
CREATE FUNCTION foo_out()
RETURNS SETOF Integer AS
$$
BEGIN
RETURN QUERY
SELECT 1 as foo;
END
$$
LANGUAGE plpgsql;
CREATE FUNCTION foo1()
RETURNS SETOF my_test
AS $$
DECLARE
x my_test;
BEGIN
FOR x IN
SELECT foo_out()
LOOP
RETURN NEXT x;
END LOOP;
END;
$$
LANGUAGE 'plpgsql';
select * from foo1();
But why does the same code return:
ERROR: invalid input syntax for integer: (1)
if i changed the return type to:
CREATE FUNCTION foo_out()
RETURNS SETOF my_test
What should also be integer
!? This might be the case where the system differs between an integer and a custom type that includes an integer. What needs to be changed to use custom types here?
source to share
my_test
is a compound type, a string type that contains one integer field. It is not integer
.
When assigning to post or string type in FOR
loop all output columns from query formline. Yours SELECT
should return a single, unlearned column integer
, which is then nested in the row type that matches your custom type my_test
.
You can also assign a list of scalar variables in a loop FOR
, in which case the columns from the query are assigned left to right to the variables as they are - does not form a string.
If the column has a row type, you have one nesting level for many. The textual representation of a string containing an integer field is "(1)" (with parentheses!) And what you see in the error message.
You can fix this fetching integer field from the notation string :
SELECT (foo_out()).*
Or (more efficiently for multiple columns) by decomposing with:
SELECT * FROM foo_out()
Sample code
CREATE FUNCTION foo_out()
RETURNS SETOF my_test AS
$$
SELECT '(1)'::my_test
$$
LANGUAGE sql;
CREATE FUNCTION foo1()
RETURNS SETOF my_test AS
$$
DECLARE
x my_test;
BEGIN
FOR x IN
SELECT * FROM foo_out()
LOOP
RETURN NEXT x;
END LOOP;
END
$$
LANGUAGE plpgsql;
Do not include the name of the language plpgsql
. This is an identifier.
Remember that a loop is very rarely needed, as most problems are more efficiently solved with a set-based approach (SQL only).
Similar answers from Craig and Paul:
source to share