How to Display Column Headers Returned by Postgresql Function

How to display column headers returned by PostgreSQL function?

Use your set returning function in the FROM clause

SELECT * FROM userbyid(1);

as opposed to

SELECT userbyid(1);

Here is dbfiddle demo

Sample output:

 id | username |       email       |       last_login
----+----------+-------------------+------------------------
  1 | user1    | user1@example.com | 2017-06-13 12:00:00-04

How to derive a column name in the return type from input parameters to the function?

How to derive a column name in the return type from input parameters to the function?

The short answer: Not possible.

SQL is very rigid about column data types and names. Those have to be declared before or at call time at the latest. No exceptions. No truly dynamic column names.

I can think of 3 half-way workarounds:

1. Column aliases

Use your function as is (or rather the audited version I suggest below) and add column aliases in the function call:

SELECT * FROM func_getratio_laglag('order_first',1,'order_last',0,'customers_hist')
AS f(date_t, customer_code, index, order_first_1_order_last_0)

I would do that.

2. Column definition list

Create your function to return anonymous records:

RETURNS SETOF record

Then you have to provide a column definition list with every call:

SELECT * FROM func_getratio_laglag('order_first',1,'order_last',0,'customers_hist')
AS f(date_t timestamp, customer_code text, index text, order_first_1_order_last_0 real)

I would not do that.

3. Use a registered row type as polymorphic input / output type.

Mostly useful if you happen to have row types at hand. You could register a row type on the fly by crating a temporary table, but that seems like overkill for your use case.

Details in the last chapter of this answer:

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

Function audit

Use format() to make building query string much more safe and simple.

Read the manual if you are not familiar with it.

CREATE OR REPLACE FUNCTION func_getratio_laglag(
                           _numerator_lag   text, _n1 int
                         , _denominator_lag text, _n2 int
                         , _table regclass)
   RETURNS TABLE (date_t timestamp, customer_code text, index text, ratio real) AS
$func$
BEGIN
   RETURN QUERY EXECUTE format (
     'SELECT date_t, customer_code, index
           , (lag(%I, %s) OVER w / lag(%I, %s) OVER w)  -- data type must match
      FROM   %s 
      WINDOW w AS (PARTITION BY customer_code ORDER BY date_t)'
    , _numerator_lag, _n1, _denominator_lag, _n2, _table::text
   );
END
$func$ LANGUAGE plpgsql;

Note the data type regclass for the table name. That's my personal (optional) suggestion.

• Table name as a PostgreSQL function parameter

Aside: I would also advise not to use mixed-case identifiers in Postgres.

• Are PostgreSQL column names case-sensitive?

PostgreSQL function with table and column names as parameters,and would return min and max of that column (any type) as an array?

You can't do this with the standard polymorphic types, but you could do this with jsonb.

CREATE OR REPLACE FUNCTION minmax(tablename regclass, columnname character varying)
RETURNS JSONB AS
$function$
DECLARE
  minmaxa jsonb;
BEGIN
   EXECUTE FORMAT('SELECT jsonb_agg(foo) AS %I FROM (
            (SELECT min(%I) AS foo FROM %I)
            UNION
            (SELECT max(%I) AS foo FROM %I)
    ) AS foobar'
       , columnname
       , columnname, tablename
       , columnname, tablename)
   INTO minmaxa;
   RETURN minmaxa;
END
$function$
STABLE
LANGUAGE plpgsql;

-- Test with integers
CREATE TABLE test (a int);
insert into test (a) values (1), (2), (3);
select minmax('test'::regclass, 'a');
 minmax
--------
 [1, 2]
(1 row)

-- Test with timestamps
create table test2 (a timestamp);
insert into test2 values ('2012-01-01T00:00:00'), ('2015-01-01T00:00:00');
select minmax('test2'::regclass, 'a');
                     minmax
------------------------------------------------
 ["2012-01-01T00:00:00", "2015-01-01T00:00:00"]
(1 row)

I used regclass instead of string for the tablename to help find the correct table according to your search path.

postgres functions loses column names

This is a bug in pgAdmin (reported here).

The function definition is stored correctly when you first create it. pgAdmin displays it incorrectly, but calls to the function should still behave as expected.

The problem only arises when you take pgAdmin's CREATE OR REPLACE statement and run it. The recreation will appear to succeed, but the function will fail at runtime due to the missing variable declaration.

As long as you remember to fix the return type every time you recreate the function, you shouldn't have a problem.

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?

Return list of columns via function in PostgreSQL

There is no need for loop at all. You could simply use string_agg:

SELECT STRING_AGG(column_name, ',')
FROM information_schema.columns
WHERE table_schema = 'public'
 AND  table_name =  'testx';

You could use it as subquery in your code.

Rextester Demo

---

Or if you need a function:

CREATE OR REPLACE FUNCTION myschema.return_column_list(tab_name TEXT)
RETURNS text AS
$BODY$
BEGIN
  RETURN (SELECT string_agg(column_name, ',') FROM information_schema.columns
                    WHERE table_schema = 'myschema'
                    AND table_name = format('%s', tab_name));
END;
BODY$ LANGUAGE 'plpgsql';

Rextester Demo 2

---

As for variable initialization. Default value is NULL and NULL concatenated to column_name is still NULL.

SELECT NULL || 'a';
-- NULL

Function Returning Set Of Columns

There is no need to for dynamic SQL or PL/pgSQL.

create or replace function Table_Schema(schemaname text, tablename text) 
 RETURNS SETOF information_schema.columns
 as
$body$
 select * 
 from information_schema.columns 
 WHERE table_name = tablename
   and table_schema = schemaname;
$body$
language sql;

But to export all columns into a CSV file, you don't really need a function at all:

\copy (select * from information_schema.columns where table_schema = 'public' order by schema_name, table_name, ordinal_position) to '/path/to/output.csv' with (format csv)

Record returned from function has columns concatenated

Generally, to decompose rows returned from a function and get individual columns:

SELECT * FROM account_servicetier_for_day(20424, '2014-08-12');

---

As for the query:

Postgres 9.3 or newer

Cleaner with JOIN LATERAL:

SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes
     , a.username, a.accountid, a.userid
     , f.*   -- but avoid duplicate column names!
FROM   account_tab a
     , account_servicetier_for_day(a.accountid, '2014-08-12') f  -- <-- HERE
WHERE  a.isdsl = 1
AND    a.dslservicetypeid IS NOT NULL
AND    NOT EXISTS (
   SELECT FROM dailyaccounting_tab
   WHERE  day = '2014-08-12'
   AND    accountid = a.accountid
   )
ORDER  BY a.username;

The LATERAL keyword is implicit here, functions can always refer earlier FROM items. The manual:

LATERAL can also precede a function-call FROM item, but in this
case it is a noise word, because the function expression can refer to
earlier FROM items in any case.

Related:

• Insert multiple rows in one table based on number in another table

Short notation with a comma in the FROM list is (mostly) equivalent to a CROSS JOIN LATERAL (same as [INNER] JOIN LATERAL ... ON TRUE) and thus removes rows from the result where the function call returns no row. To retain such rows, use LEFT JOIN LATERAL ... ON TRUE:

...
FROM  account_tab a
LEFT  JOIN LATERAL account_servicetier_for_day(a.accountid, '2014-08-12') f ON TRUE
...

Also, don't use NOT IN (subquery) when you can avoid it. It's the slowest and most tricky of several ways to do that:

• Select rows which are not present in other table

I suggest NOT EXISTS instead.

Postgres 9.2 or older

You can call a set-returning function in the SELECT list (which is a Postgres extension of standard SQL). For performance reasons, this is best done in a subquery. Decompose the (well-known!) row type in the outer query to avoid repeated evaluation of the function:

SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes
     , a.username, a.accountid, a.userid
     , (a.rec).*   -- but be wary of duplicate column names!
FROM  (
   SELECT *, account_servicetier_for_day(a.accountid, '2014-08-12') AS rec
   FROM   account_tab a
   WHERE  a.isdsl = 1
   AND    a.dslservicetypeid Is Not Null
   AND    NOT EXISTS (
       SELECT FROM dailyaccounting_tab
       WHERE  day = '2014-08-12'
       AND    accountid = a.accountid
      )
   ) a
ORDER  BY a.username;

Related answer by Craig Ringer with an explanation, why it's better not to decompose on the same query level:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

Postgres 10 removed some oddities in the behavior of set-returning functions in the SELECT:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

---

Related Topics