Postgres function returning table not returning data in columns

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
(...)

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$;