On 12/13/06, Kevin Murphy <murphy@genome.chop.edu> wrote:
> I'd like to have a function that acts as a row filter (that can
> optionally expand each row into multiple rows), but I don't know how to
> wangle this such that the output is not enclosed in parentheses, i.e.
> what I'm getting now is a single column of a composite type instead of
> multiple columns matching the original table layout.
>
> -- SQL front-end for filter function
> CREATE OR REPLACE FUNCTION explode(sometable) RETURNS SETOF sometable as $$
> SELECT * FROM foo($1) AS t;
> $$ LANGUAGE SQL STRICT IMMUTABLE;
>
> select explode(sometable.*) from sometable;
> explode
> -----------
> (A,1)
> (A,1)
> (B,2)
> (B,2)
functions defined in the sql language (as opposed to pl/pgsql) allow
you to call them without explicitly using from...if you want to
expand, just select from your result as you would expand any row
variable. basically, have you tried:
select (explode(sometable.*)).* from sometable;
merlin