How to display column headers returned by PostgreSQL function?

Question

I have the following PostgreSQL function which returns multiple columns from a table:

CREATE OR REPLACE FUNCTION userbyid(id integer)
RETURNS TABLE(id int, username character varying(30), email character varying(254), last_login timestamp with time zone) AS
$$
SELECT
    id,
    username,
    email,
    last_login
FROM
    auth_user
WHERE
    id = $1;
$$
LANGUAGE 'sql';

The result it returns looks like this:

                        userbyid
--------------------------------------------------------------
(2, smith, [email protected], "2017-06-04 19:47:49.472259+00")

Is it possible to display the output with the correct column headers, something like this:

id         username       email               last_login
--------------------------------------------------------------
2          smith          [email protected]   2017-06-04

I'm looking at the CREATE FUNCTION document page and it isn't clear how to do this. I've also searched online and haven't seen an article that discusses this.

Immiscible

Answer 1

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    | [email protected] | 2017-06-13 12:00:00-04

Perilymph

Answer 2

You need to format PostgreSQL to show the colum headers; Set it with:

\t

this can be seen in PSQL help

\?

Constitutional

This one is confusing because you get the message tuples only is on or tuples only is off. Tuples as in rows of values only, without the column headers. – Illampu

Answer 3

You can use "as" in your query. Select Id as id, username as username....

Hesperian

The 'as' works for me : SELECT ar.invnumber as "Inv No", ar.amount as "Inv Total", ar.transdate as "Inv Date", customer.name as "Customer", acc_trans.amount as "Amount Recd", acc_trans.transdate as "Date Recd" – Greywacke