Postgres function returning table not returning da

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

Of course you can do this by putting the function call in the FROM clause, like Eric Brandstetter correctly answered. However, this is sometimes complicating in a query that already has other things in the FROM clause. To get the individual columns that the function returns, you can use this syntax:

SELECT (testfunction()).*

Or to get only the column called "a":

SELECT (testfunction()).a

Place the whole function, including the input value(s) in parenteses, followed by a dot and the desired column name, or an asterisk.

To get the column names that the function returns, you'll have to either:

• check the source code
• inspect the result of the function first, like so : SELECT * FROM testfunction() .

The input values can still come out of a FROM clause. Just to illustrate this, consider this function and test data:

CREATE FUNCTION funky(a integer, b integer)
RETURNS TABLE(x double precision, y double precision) AS $$
 SELECT a*random(), b*random();
$$ LANGUAGE SQL;

CREATE TABLE mytable(a integer, b integer);
INSERT INTO mytable
    SELECT generate_series(1,100), generate_series(101,200);

You could call the function "funky(a,b)", without the need to put it in the FROM clause:

SELECT (funky(mytable.a, mytable.b)).*
FROM mytable;

Which would result in 2 columns:

         x         |         y         
-------------------+-------------------
 0.202419687062502 |   55.417385618668
  1.97231830470264 |  63.3628275180236
  1.89781916560605 |  1.98870931006968
(...)

Answer 2

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.
• 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 leads to confusing errors. See
  • Are PostgreSQL column names case-sensitive?

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;