CODEWITHSUNDEEP
sqldatabasefunctionpostgresqlplpgsql
EMP
EMP

Reputation: 61971

Call PostgreSQL function with multiple composite output parameters

I'd like to define a function in PostgreSQL 9.1 that takes multiple INOUT parameters of composite types, but I don't know how 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 execute 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

Upvotes: 3

Views: 5308

Answers (1)

David-SkyMesh
David-SkyMesh

Reputation: 5171

I don't think it has anything to do with your input types, or the number of them.

Don't return a RECORD, return a real composite type (defined with CREATE TYPE).

The error record or row variable cannot be part of multiple-item INTO list is because you're trying to nest 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 concrete 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)

Upvotes: 3

Related Questions