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

Viewed 24939

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?

1 Answers
Related