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

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

Responses

Browse pgsql-novice by date

  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