function accepting and returning rows; how to avoid parentheses

Поиск
Список
Период
Сортировка
От Kevin Murphy
Тема function accepting and returning rows; how to avoid parentheses
Дата
Msg-id 457F4C91.7050702@genome.chop.edu
обсуждение исходный текст
Ответы Re: function accepting and returning rows; how to avoid parentheses  ("Merlin Moncure" <mmoncure@gmail.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Roderick A. Anderson"
Дата:
Сообщение: changing the permission of _lots_ of tables
Следующее
От: Michael Fuhr
Дата:
Сообщение: Re: resetting sequence to cur max value