PostgreSQL function call with multiple composite out parameters
I would like to define a function in PostgreSQL 9.1 that takes multiple INOUT parameters of composite types, but I don't know what to call it.
Eg.
CREATE TYPE my_type_a AS (a integer, b float);
CREATE TYPE my_type_b AS (c boolean, d varchar(5));
CREATE FUNCTION my_complex_func(INOUT a my_type_a, INOUT b my_type_b)
RETURNS RECORD
'...'
LANGUAGE plpgsql;
The definition statements run just fine, but I don't know how to call this function! I tried:
SELECT INTO a, b
a, b FROM my_complex_func(a, b);
but this gives an error:
ERROR: record or row variable cannot be part of multiple-item INTO list
+3
source to share
1 answer
I don't think it has anything to do with your input types or their number.
Do not return RECORD, return the real composite type (defined with CREATE TYPE).
The error record or row variable cannot be part of multiple-item INTO list
is when you are trying to insert a ROW inside another ROW.
This should work:
CREATE TYPE my_type_a AS (a integer, b float);
CREATE TYPE my_type_b AS (c boolean, d varchar(5));
CREATE TYPE ret_type AS (w integer, v boolean);
CREATE FUNCTION my_complex_func(INOUT a my_type_a, INOUT b my_type_b)
RETURNS ret_type as $$
...
$$ LANGUAGE plpgsql;
And then you can do:
SELECT INTO a, b
(x.comp).w, (x.comp).v
FROM (select my_complex_func(j, i) as comp) x;
This specific example works for me:
create type smelly1 as (a integer, b text);
create type smelly2 as (a boolean, b float);
create type rettype as (w integer, v boolean);
create function foo_func(n smelly1, m smelly2) returns rettype as $$
declare
f_ret rettype;
begin
f_ret.w := n.a;
f_ret.v := m.a;
return f_ret;
end;
$$ language plpgsql;
select (x.comp).w, (x.comp).v from
(select foo_func('(4, hello)'::smelly1, '(true,3.14)'::smelly2) as comp) x;
returns:
w | v
---+---
4 | t
(1 row)
+3
source to share