Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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
share|improve this question

1 Answer

up vote 2 down vote

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)
share|improve this answer
Oops, forgot write field-name (comp) from subquery (x) – David-SkyMesh Apr 5 '12 at 8:40
Yes, but what I need to return is two instances of a complex type (the same type in this case, but could be different). I have to return record when there are OUT parameters: ERROR: function result type must be record because of OUT parameters. Of course, if there's some way to do this without OUT parameters that would be OK, too. – EMP Apr 5 '12 at 8:45
Added example... – David-SkyMesh Apr 5 '12 at 8:47
what are you using the OUT parameters for? – David-SkyMesh Apr 5 '12 at 8:47
1  
Whatever you use the OUT params for, you should be able to return in an even-more-composite type :-) – David-SkyMesh Apr 5 '12 at 8:48
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.