Postgres Function Returning Table Not Returning Data in Columns

Postgres function returning table not returning data in columns

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

Function not returning table despite it being the return type

It's a bad idea to name your database objects the same names as system functions. When you think you're calling your function, you can't actually guarantee that the database will actually call it. Here is what is actually called in your case.

Therefore, rename your function, for example, to user_func_like, and the error will disappear. Or specify the schema name like user_schema.like(x,y). But better to use non-system names

How to return uncertain number columns of a table from a postgresql function?

When a function returns anonymous records

RETURNS SETOF record

you have to provide a column definition list when calling it with SELECT * FROM. SQL demands to know column names and types to interpret *. For registered tables and types this is provided by the system catalog. For functions you need to declare it yourself one way or the other. Either in the function definition or in the call. The call could look like @Craig already provided. You probably didn't read his answer carefully enough.

Depending on what you need exactly, there are a number of ways around this, though:

1) Return a single anonymous record

Example:

CREATE OR REPLACE FUNCTION myfunc_single()  -- return a single anon rec
  RETURNS record AS
$func$
DECLARE
   rec record;
BEGIN
   SELECT into rec  1, 'foo';  -- note missing type for 'foo'
   RETURN rec;
END
$func$  LANGUAGE plpgsql;

This is a very limited niche. Only works for a single anonymous record from a function defined with:

RETURNS record

Call without * FROM:

SELECT myfunc_single();

Won't work for a SRF (set-returning function) and only returns a string representation of the whole record (type record). Rarely useful.

To get individual cols from a single anonymous record, you need to provide a column definition list again:

SELECT * FROM myfunc_single() AS (id int, txt unknown); -- note "unknown" type

2) Return well known row type with a super-set of columns

Example:

CREATE  TABLE t (id int, txt text, the_date date);
INSERT INTO t VALUES  (3, 'foz', '2014-01-13'), (4, 'baz', '2014-01-14');

CREATE OR REPLACE FUNCTION myfunc_tbl()  -- return well known table
  RETURNS SETOF t AS
$func$
BEGIN
   RETURN QUERY
   TABLE t;
   -- SELECT * FROM t;  -- equivalent
END
$func$  LANGUAGE plpgsql;

The function returns all columns of the table. This is short and simple and performance won't suffer as long as your table doesn't hold a huge number of columns or huge columns.

Select individual columns on call:

SELECT id, txt FROM myfunc_tbl();
SELECT id, the_date FROM myfunc_tbl();

-> SQLfiddle demonstrating all.

3) Advanced solutions

This answer is long enough already. And this closely related answer has it all:

Refactor a PL/pgSQL function to return the output of various SELECT queries

Look to the last chapter in particular: Various complete table types

why postgres functions returns result in one column?

If you want the result as a set of columns, then you need:

SELECT * FROM public.get_direction();

PostgreSQL call function returning setof record with table and additional columns

If the query in your function should return a my_table_name (a “whole-row reference), you should write it like

SELECT mtn, count(*) OVER () ...

Function returning a table for given table and column names

SQL is a strictly typed language and Postgres functions must declare their return type. Returning a variable number of columns from a function is only possible with workarounds, like polymorphic types. See:

• How to return a table by rowtype in PL/pgSQL

But we can't work with the row type in your case, as that varies from database to database. The remaining option: return anonymous records and provide a column definition list with every call. I don't usually recommend this, as providing a column definition list with every call can be tedious - and often pointless. But yours might be one of the rare use cases where it makes sense.

Still, you have to know the data type of possibly missing columns. I'll assume integer for the purpose of this demo. Else you have to pass data types additionally and build the query accordingly.

CREATE OR REPLACE FUNCTION f_dynamic_select(_tbl regclass
                                          , _cols VARIADIC text[])  -- ①
  RETURNS SETOF record     -- ② anonymous records
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE    -- ③ dynamic SQL
   format(
      'SELECT %s FROM %s'  -- ④ safe against SQLi
    , (
      SELECT string_agg(COALESCE(quote_ident(a.attname)
                              , '0 AS ' || quote_ident(t.col)  -- assuming integer!
                                ), ', ' ORDER  BY t.ord) -- ⑤
      FROM   unnest(_cols) WITH ORDINALITY t(col, ord)   -- ⑤
      LEFT   JOIN pg_attribute a ON a.attrelid = _tbl    -- ⑥
                                AND a.attnum > 0
                                AND NOT a.attisdropped 
                                AND a.attname = t.col
      )
    , _tbl
   );
END
$func$;

Call (important!)

SELECT *
FROM   f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int); -- ② column definition list

Your example call, with expressions based on these columns:

SELECT pool, case when spa = 1 then 1 else 0 end as has_spa  -- ⑦ expressions
     , sauna, house_size
     , case when no_rooms > 2 then 1 else 0 end as rooms 
FROM f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int);

db<>fiddle here

① The function takes a table name as regclass type. See:

• Table name as a PostgreSQL function parameter

... followed by an arbitrary list of column names - in meaningful order. VARIADIC should be convenient for this. See:

• Pass multiple values in single parameter

Note that we pass column names as case-sensitive, single-quoted strings. Not (double-quoted) identifiers.

② This may be the first time ever I recommend returning anonymous records from a function - after close to 1000 answers on the [plpgsql] tag. The manual:

If the function has been defined as returning the record data type,
then an alias or the key word AS must be present, followed by a column
definition list in the form ( column_name data_type [, ... ]). The
column definition list must match the actual number and types of
columns returned by the function.

③ The manual on dynamic SQL.

④ Safe against SQL injection, because the table name is passed as regclass, and the SELECT list is concatenated using quote_ident() carefully. See:

• Define table and column names as arguments in a plpgsql function?

⑤ Use WITH ORDINALITY to preserve original order of columns. See:

• PostgreSQL unnest() with element number

⑥ LEFT JOIN to the system catalog pg_attribute to identify existing columns. See:

• Select columns with particular column names in PostgreSQL

⑦ Move expressions building on the passed columns to the outer SELECT.

---

Disclaimer: I would only introduce this level of sophistication if I had to. Maybe you can work with simple views in each database after all?

Postgres Functions: Getting the Return Table Column Details

You can query the catalog table pg_proc, which contains all the required information:

SELECT coalesce(p.na, 'column' || p.i),
       p.ty::regtype,
       p.i
FROM pg_proc AS f
   CROSS JOIN LATERAL unnest(
                         coalesce(f.proallargtypes, ARRAY[f.prorettype]),
                         f.proargmodes,
                         f.proargnames
                      )
                      WITH ORDINALITY AS p(ty,mo,na,i)
WHERE f.proname = 'interval_ok'
  AND coalesce(p.mo, 'o') IN ('o', 't')
ORDER BY p.i;

---

Related Topics