Thread: RETURN QUERY SELECT & TYPE

Hello

2010/8/10 screamge <screamge@gmail.com>:
> Here is code of first procedure:
> CREATE TYPE some_item AS
> (id integer,
> title character varying,
> ...
> );
>
>
> CREATE OR REPLACE FUNCTION some_func (integer) RETURNS some_item AS
> ...
> itemid ALIAS for $1;
> resulter some_item%rowtype;
>
> ...
> SELECT INTO resulter
> n_id, t_title FROM some_table WHERE n_id = itemid;
> RETURN resulter;
>
>
> I want to call some_func from another procedure and get result set of
> some_items type. Something like this:
>
> CREATE OR REPLACE FUNCTION other_func (integer) RETURNS SETOF some_item AS
> ...
> RETURN QUERY SELECT some_func(id) FROM another_table;
> ;

hmm .. the I see it. PostgreSQL expect list of scalar values, but you
are return a composite value. Pg does packing to composite type
automatically. What you can do. Unpack a composite before (with
subselect as protection to duplicate func call):

RETURN QUERY SELECT (some_func).* FROM (SELECT some_func(id) FROM
another_table) xx;

Regards

Pavel Stehule




>
>
> But when i run other_func i get:
> ERROR: structure of query does not match function result type

On Tue, Aug 10, 2010 at 2:27 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> 2010/8/10 screamge <screamge@gmail.com>:
>> Here is code of first procedure:
>> CREATE TYPE some_item AS
>> (id integer,
>> title character varying,
>> ...
>> );
>>
>>
>> CREATE OR REPLACE FUNCTION some_func (integer) RETURNS some_item AS
>> ...
>> itemid ALIAS for $1;
>> resulter some_item%rowtype;
>>
>> ...
>> SELECT INTO resulter
>> n_id, t_title FROM some_table WHERE n_id = itemid;
>> RETURN resulter;
>>
>>
>> I want to call some_func from another procedure and get result set of
>> some_items type. Something like this:
>>
>> CREATE OR REPLACE FUNCTION other_func (integer) RETURNS SETOF some_item AS
>> ...
>> RETURN QUERY SELECT some_func(id) FROM another_table;
>> ;
>
> hmm .. the I see it. PostgreSQL expect list of scalar values, but you
> are return a composite value. Pg does packing to composite type
> automatically. What you can do. Unpack a composite before (with
> subselect as protection to duplicate func call):
>
> RETURN QUERY SELECT (some_func).* FROM (SELECT some_func(id) FROM
> another_table) xx;

right -- when you have only a single composite type going out of the
function, postgres assumes you are returning its fields, not the type
itself.  This also affects how you will access the results of the
function in the calling query.  In non 'set returning' functions it's
not as noticeable if you are using variable assignment to set
composite memebers.

If you wanted to force a composite type return from a function (note:
I use composite types and have never had a reason to do this) you
could wrap the type to do it:

create table foo(foo_id) int;
create type foowrap(foo foo);
create function get_foo() returns setof foowrap as
$$
  begin
    return query select row(1)::foo;
  end;
$$ language plpgsql;

postgres=# select * from get_foo();
 foo
-----
 (1)
(1 row)

A more direct way to do this is to declare the function without the
wrap and simply don't use 'select *':
create function get_foo() returns setof foo as
$$
  begin
    return query select 1;
  end;
$$ language plpgsql;

postgres=# select get_foo();
 get_foo
---------
 (1)
(1 row)

postgres=# select * from get_foo();
 foo_id
--------
      1
(1 row)

merlin