Search Postgresql Archives

function accepting and returning rows; how to avoid parentheses

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux