CODEWITHSUNDEEP
sqlpostgresqlset-returning-functions
Jim
Jim

Reputation: 14280

How to display column headers returned by PostgreSQL function?

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.

Upvotes: 8

Views: 13840

Answers (3)

Isaac Borbon Miquirray
Isaac Borbon Miquirray

Reputation: 109

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

\t

this can be seen in PSQL help

\?

Upvotes: 7

peterm
peterm

Reputation: 92795

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

Upvotes: 3

sss
sss

Reputation: 5

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

Upvotes: 0

Related Questions