sql - Postgres function returning table not returning data in columns

Question

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 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)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _a int := 0;
   _b int := 0;
BEGIN
   CREATE TABLE tempTable AS SELECT _a, _b;
   RETURN QUERY SELECT * FROM tempTable;
   DROP TABLE tempTable;
END
$func$;

In particular:

The DECLARE key word is only needed once.

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

Don't use unquoted CaMeL-case identifiers in Postgres. It works, unquoted identifiers are cast to lower case, but it can lead to confusing errors. See:

• Are PostgreSQL column names case-sensitive?

The temporary table in the example is completely useless (probably over-simplified). The example as given boils down to:

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