带有RETURNS TABLE（id整数）的PostgreSQL存储过程返回所有NULL

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.

我在PostgreSQL 8.4中有一个存储过程,它根据作为参数传入的整数值调用另一个存储过程。调用那些存储过程,使它们返回一个整数列的关系。我遇到的问题是外部存储过程总是返回具有正确行数但具有所有id的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?

那么为什么conditional_relation_return会返回所有NULL?

1 个解决方案

#1

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).

spa的id与out参数id(RETURNS TABLE(id integer))冲突。 Postgresql 8.4没有抱怨,它从out参数id中选择id而不是saner one(spa的id)。

Postgresql 9.1's complain on your original code:

Postgresql 9.1抱怨您的原始代码:

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:

要解决此问题,请完全限定查询中的ID:

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.

Postgresql尊重您从RETURNS TABLE中选择的列名称。它仍然将x.id插入到RETURNS TABLE的id中。因此,即使您决定重命名RETURNS TABLE返回列的名称,它仍然会将x.id插入该名称,例如

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

注意hahah专栏