Satish Sharma Satish Sharma - 1 month ago 8
SQL Question

Postgres function returning table not returning data in columns

I have a Postgres function which is 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 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?

Answer

To get individual columns instead of the composite 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.
  • Avoid declaring parameters that are already (implicitly) declared as OUT parameters in the RETURNS TABLE (...) clause.