PostgreSQL stored procedure with RETURNS TABLE(id integer) returning all NULLs

Question

I have a stored procedure in PostgreSQL 8.4 that calls another stored procedure depending on the integer value passed in as a parameter. Those stored procedures are called such that they should return a relation with one integer column. The problem I am having is that the outer stored procedure always returns a relation with the correct number of rows but with all of the id's NULL.

Here is the stored procedure reduced to its simplest form:

CREATE OR REPLACE FUNCTION spa(count integer) 
RETURNS TABLE (id integer) AS $$
BEGIN
    RETURN QUERY SELECT generate_series(1, count);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION spb(count integer) 
RETURNS TABLE (id integer) AS $$
BEGIN
    RETURN QUERY SELECT generate_series(1, count);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION conditional_relation_return(objectType integer, count integer) 
RETURNS TABLE (id integer) AS $$
BEGIN
    IF objectType = 1 THEN
        RETURN QUERY SELECT id FROM spa(count);
    ELSIF objectType = 2 OR objectType = 3 THEN
        RETURN QUERY SELECT id FROM spb(count);
    END IF;

END;
$$ LANGUAGE plpgsql;

And if you call it:

# select * from conditional_relation_return(1, 2);
 id 
----


(2 rows)

Or more specifically:

# select count(*) from conditional_relation_return(1, 2) where id is null;
 count 
-------
     2
(1 row)

But if you call one of the referenced stored procedures, you get the correct results:

# select * from spa(2);
 id 
----
  1
  2
(2 rows)

So why does conditional_relation_return return all NULLs?

Michael Buen · Accepted Answer · 2012-07-14 11:54:30Z

The id of spa conflicts with the out parameter id (RETURNS TABLE (id integer)). Postgresql 8.4 doesn't complain, it chooses id from out parameter id instead of the saner one(id of spa).

Postgresql 9.1's complain on your original code:

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id FROM spa(count)
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT id FROM spa(count)
CONTEXT:  PL/pgSQL function "conditional_relation_return" line 4 at RETURN QUERY

To fix it, fully qualify the id on your query:

CREATE OR REPLACE FUNCTION conditional_relation_return(
    objectType integer, count integer) 
RETURNS TABLE (id integer) AS $$
BEGIN
    IF objectType = 1 THEN
        RETURN QUERY SELECT x.id FROM spa(count) as x;
    ELSIF objectType = 2 OR objectType = 3 THEN
        RETURN QUERY SELECT x.id FROM spb(count) as x;
    END IF;

END;
$$ LANGUAGE plpgsql;

Output:

test=# select * from conditional_relation_return(1, 2);
 id 
----
  1
  2
(2 rows)

Postgresql honors the column(s) name you choose from your RETURNS TABLE. It still slot x.id to the id of your RETURNS TABLE. So, even you decided to rename your RETURNS TABLE return column's name, it will still slot x.id to that name, e.g.

CREATE OR REPLACE FUNCTION conditional_relation_return(
    objectType integer, count integer) 
RETURNS TABLE (hahah integer) AS $$
BEGIN
    IF objectType = 1 THEN
        RETURN QUERY SELECT x.id FROM spa(count) as x;
    ELSIF objectType = 2 OR objectType = 3 THEN
        RETURN QUERY SELECT x.id FROM spb(count) as x;
    END IF;

END;
$$ LANGUAGE plpgsql;

Output:

test=# select * from conditional_relation_return(1, 2);
 hahah 
-------
     1
     2
(2 rows)

Notice the hahah column

How funny. You are right. 8.4 silently swallows the ambiguous id error. Thanks!

Collectives™ on Stack Overflow

PostgreSQL stored procedure with RETURNS TABLE(id integer) returning all NULLs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related