Geek asks and answers

Postgres function returns a table that does not return data in columns

I have a Postgres function returning a table:

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 does not return data as a row or column. Instead, it returns data as:

(0,0)

This causes a problem in the cfquery Coldfusion block when retrieving data. How to get data in rows and columns when a table returns from this function? In other words: why doesn't the PL / pgSQL function return data as columns?

+5
source share
1 answer

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

SELECT * FROM testfunction();

Just as you 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 .
  • , () OUT RETURNS TABLE (...).
  • CaMeL-case Postgres. , , , .

(, ). :

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

More articles:


All Articles