Postgres函数返回表不返回列中的数据

I have a Postgres function which is returning a table:

我有一个Postgres函数返回一个表:

CREATE OR REPLACE FUNCTION testFunction() RETURNS TABLE(a int, b int) AS
$BODY$
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
BEGIN
CREATE TABLE tempTable AS SELECT a, b;
RETURN QUERY SELECT * FROM tempTable; 
DROP TABLE tempTable;
END;
$BODY$
LANGUAGE plpgsql;

This function is not returning data in row and column form. Instead it returns data as:

此函数不以行和列形式返回数据。相反,它返回数据为:

(0,0)

That is causing a problem in Coldfusion cfquery block in extracting data. How do I get data in rows and columns when a table is returned from this function? In other words: Why does the PL/pgSQL function not return data as columns?

这导致Coldfusion cfquery块在提取数据时出现问题。从此函数返回表时,如何获取行和列中的数据?换句话说:为什么PL / pgSQL函数不会将数据作为列返回?

1 个解决方案

#1

To get individual columns instead of the row type, call the function with:

要获取单个列而不是行类型,请使用以下命令调用该函数:

SELECT * FROM testfunction();

Just like you would select all columns from a table.
Also consider this reviewed form of your test function:

就像你从表中选择所有列一样。还要考虑这个评估的测试功能形式:

CREATE OR REPLACE FUNCTION testfunction()
  RETURNS TABLE(a int, b int) AS
$func$
DECLARE
   _a int := 0;
   _b int := 0;
BEGIN
   CREATE TEMP TABLE tbl AS SELECT _a, _b;
   RETURN QUERY SELECT * FROM tbl;
   DROP TABLE tempTable;
END
$func$  LANGUAGE plpgsql;

In particular:

DECLARE key word is only needed once.

DECLARE关键字只需要一次。

Avoid declaring parameters that are already (implicitly) declared as OUT parameters in the RETURNS TABLE (...) clause.

避免在RETURNS TABLE(...)子句中声明已经(隐式)声明为OUT参数的参数。

Don't use unquoted CaMeL-case identifiers in Postgres. It works, unquoted identifiers a cast to lower case, but it leads to confusing errors. See
- Are PostgreSQL column names case-sensitive?

不要在Postgres中使用不带引号的CaMeL案例标识符。它可以工作,不带引号的标识符可以转换为小写,但是会导致混淆错误。请参阅PostgreSQL列名称区分大小写?

The temporary table is completely useless in the example (probably over-simplified). You could reduce to:

临时表在示例中完全没用(可能过度简化)。你可以减少到:

CREATE OR REPLACE FUNCTION testfunction(OUT a int, OUT b int) AS
$func$
BEGIN
   a := 0;
   b := 0;
END
$func$  LANGUAGE plpgsql;

#1