Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I perform a Union with the result of a function returning SETOF

How do I perform a Union with the result of a function returning SETOF

From
rox
Date:
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)

hq4_unpack(pts.*).id doesn't appear to work.

thanks,

Roxanne
Postgres version 8.4

Re: How do I perform a Union with the result of a function returning SETOF

From
rox
Date:
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