From: | rox <rox(at)tara-lu(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | How do I perform a Union with the result of a function returning SETOF <table> |
Date: | 2011-11-04 20:09:59 |
Message-ID: | 745b40521cf5420bb540927ec1b29b77@mail.webfaction.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tair Sabirgaliev | 2011-11-05 11:05:27 | Re: WARNING: pgstat wait timeout |
Previous Message | Jean-Yves F. Barbier | 2011-11-04 19:40:12 | Re: Developing a searching engine |