Postgres Pro
Downloads
Home   >   mailing lists

Re: How do I perform a Union with the result of a function returning SETOF - Mailing list pgsql-novice
From rox
Subject Re: How do I perform a Union with the result of a function returning SETOF
Date
Msg-id bf982753eb06cbe04cd2245a1233596c@mail.webfaction.com
Whole thread Raw
In response to How do I perform a Union with the result of a function returning SETOF  (rox <rox@tara-lu.com>)
List pgsql-novice
Tree view 
On Fri, 04 Nov 2011 16:09:59 -0400, rox wrote:
> I'm sure I'm just missing something simple, but...
>
> We have a table:
>
> point_table
> (  id serial,
>    fk_id integer,
>    loc geometry,
>    valid boolean
> )
>
> We have a function that performs something like the following:
>
> CREATE OR REPLACE FUNCTION hq4_unpack(template point_table)
> RETURNS SETOF point_table
> $BODY$
> DECLARE
>     point point_table;
> begin
>
>     Loop creating points setting:
>       point.id = template.id;
>       point.fk_id = template.fk_Id;
>       point.loc = constructed geometry;
>       point.status = template.status;
>
>       RETURN next POINT;
>     END LOOP;
>
>     RETURN;
> END;
>
> I have a query that I want to do the following:
>
> select pts.* from point_table pts where ST_GeometryType(loc) =
> 'ST_POINT'
> union
> select hq4_unpack(pts.*) from point_table pts where
> ST_GeometryType(loc) <> 'ST_POINT'
>
> However the return from the function appears to be 1 column while the
> select on the table is .. multiple.
> Is there an easy way to select off the columns from the returned
> SETOF tabletype? example output of function (as viewed in pgAdmin):
> (1,2,'geometry as text',t)

For anybody who runs into this same scenario...

The simply answer is to change it to
        select * from hq4_unpack()
and move the "select * from point_table..." into the function.

If you *have* to keep the query external to the function...

I've not figured out any way to do something like the following

select * from hq4_unpack((select pts.* from point_table pts where
ST_GeometryType(loc) <> 'ST_POINT'))
[this gives back "subquery must return 1 column" error]

However, you can pass the query in as a string and execute it then
looping on the results.

But in all of the above cases the function now works on a set of
records not just one record at a time.

Unless anybody has another variation I haven't thought of?

Roxanne

pgsql-novice by date:

Previous
From: Ramy Abdel-Azim
Date:
Subject: Re: complete uninstall of postgres 9.0.4
Next
From: Ramy Abdel-Azim
Date:
Subject: Re: complete uninstall of postgres 9.0.4