Postgres Pro
Downloads
Home   >   mailing lists

Thread: function accepting and returning rows; how to avoid parentheses

function accepting and returning rows; how to avoid parentheses

From
Kevin Murphy
Date:
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.

Example:

CREATE TABLE sometable (key text, value real);
INSERT INTO sometable VALUES ('A', 1);
INSERT INTO sometable VALUES ('B', 2);

-- unrealistic demo filter function
CREATE OR REPLACE FUNCTION foo(arow sometable) RETURNS SETOF sometable AS $$
DECLARE
BEGIN
    RETURN NEXT arow;
    RETURN NEXT arow;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- 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)

Thanks,
Kevin

Re: function accepting and returning rows; how to avoid parentheses

From
"Merlin Moncure"
Date:
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

Re: function accepting and returning rows; how to avoid

From
Kevin Murphy
Date:
Merlin Moncure wrote:
> 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.
>
> 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;
>
Thanks a lot.  You must have been puzzled that I got so close and failed
to figure it out.  I hadn't really understood the {row}.* notation.  In
the meantime I did an end run with an insert trigger, but at least I
understand this now.

-Kevin