Re: RETURN QUERY SELECT & TYPE

Hello

2010/8/10 screamge <screamge(at)gmail(dot)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