Postgres Pro
Downloads
Home   >   mailing lists

How do I perform a Union with the result of a function returning SETOF - Mailing list pgsql-novice
From rox
Subject How do I perform a Union with the result of a function returning SETOF
Date
Msg-id 745b40521cf5420bb540927ec1b29b77@mail.webfaction.com
Whole thread Raw
Responses Re: How do I perform a Union with the result of a function returning SETOF  (rox <rox@tara-lu.com>)
List pgsql-novice
Tree view 
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

pgsql-novice by date:

Previous
From: "Jean-Yves F. Barbier"
Date:
Subject: Re: Developing a searching engine
Next
From: Tair Sabirgaliev
Date:
Subject: Re: WARNING: pgstat wait timeout