PostgreSQL: Call a Function More Than Once in FROM Clause

Question

How can I call a function which returns records more than once in FROM clause? I understand that I have to specify a 'column definition list' when using a function that returns records. But how can I then use aliases for that function?

Example:

CREATE OR REPLACE FUNCTION foo(which_foo int) RETURNS SETOF RECORD AS
$BODY$BEGIN
IF which_foo=0 THEN
 RETURN QUERY EXECUTE 'SELECT 1::int,2::int;';
ELSE 
 RETURN QUERY EXECUTE 'SELECT 1::int,2::int;';
END IF;
END$BODY$
LANGUAGE plpgsql;

SELECT * FROM foo(0) AS (a int, b int);;
SELECT * FROM foo(1) AS (c int, d int);
SELECT * FROM foo(0) AS (a int, b int), foo(1) AS (c int, d int);

The last select statement will fail with:

ERROR:  table name "foo" specified more than once

I want to keep using the column definition list, because the function I want to use in the end has to be as generic as possible.

Clodoaldo Neto · Accepted Answer · 2013-05-20 17:18:27Z

2

SELECT f0.*, f1.*
FROM
    foo(0) AS f0 (a int, b int),
    foo(1) AS f1 (c int, d int);

answered May 20, 2013 at 17:18

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2013-05-20 17:31:17Z

0

I understand that I have to specify a 'column definition list' when using a function that returns records.

No, you do not. I wouldn't operate with anonymous records. Declare the return type, since you already know it:

CREATE OR REPLACE FUNCTION foo(which_foo int)
 RETURNS TABLE (a int, b int) AS
$func$
BEGIN
    IF which_foo = 0 THEN
        RETURN QUERY SELECT 1,2;
    ELSE 
        RETURN QUERY SELECT 1,2;
    END IF;
END
$func$ LANGUAGE plpgsql;

And assuming you do not want to combine multiple calls into one row, you should use UNION ALL:

SELECT * FROM foo(0)
UNION ALL
SELECT * FROM foo(1);

answered May 20, 2013 at 17:31

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Call a Function More Than Once in FROM Clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related