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

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