Retrieving Column Names in Postgresql Query Results

Information schema views
often simpler
(depends)
slow
preprocessed, which may or may not suit your needs
selective (users only see objects they have privileges for)
conforming to an SQL standard (that’s implemented by some of the major RDBMS)
mostly portable
across major Postgres versions
do not require much specific knowledge about Postgres
identifiers are descriptive, long and sometimes awkward
System catalogs
often more complex (depends), closer to the source
fast
complete
(system columns like
included)
not complying to an SQL standard
less portable across major Postgres versions (but basics aren’t going to change)
require more specific knowledge about Postgres
identifiers are terse, less descriptive but conveniently short
Arbitrary query
To get the same list of column names and types from a query, you could use a simple trick:
a
temporary table
from the query output, then use the same techniques as above.
The Result is :
Solution 1:
You could simply type them and concat into one column to use it as a row in outer select:
If you want to discard information about particular column if it’s value is
you can append column names conditionally (with a
statement).

Question:

I am retrieving values in
array format
using the query below and I also require the column name to be included.

CREATE OR REPLACE FUNCTION load_page(IN _session integer)
  RETURNS TABLE(col1 text, col2 text) AS
$BODY$
BEGIN
    RETURN QUERY 
    select
    (SELECT array_agg(sq.*)
    FROM (SELECT user_id, user_name 
          FROM "user" 
          ) sq
    )::text,
    (SELECT array_agg(sq.*)
    FROM (SELECT client_id, client_name ,client_desc
          FROM "clients"
          ) sq
    )::text;
END;
$BODY$ LANGUAGE plpgsql STABLE;

The Result is :

"("{""(2,Test)"",""(5,Santhosh)"",""(3,Test1)""}","{""(1,Test1,Test1)"",""(2,test2,test2)"",""(3,test3,test3)""}")"  



Solution 1:

One approach is to type the values and concatenate them into a single column. This can then be used as a row in the outer select statement.

CREATE OR REPLACE FUNCTION load_page(IN _session integer)
  RETURNS TABLE(col1 text, col2 text) AS
$BODY$
BEGIN
    RETURN QUERY 
    select
    (SELECT array_agg(sq.*)
    FROM (SELECT concat('user_id: ',user_id,', user_name: ', user_name)
          FROM "user" 
          ) sq
    )::text,
    (SELECT array_agg(sq.*)
    FROM (SELECT concat('client_id: ',client_id,', client_name: ',client_name,', client_desc: ',client_desc)
          FROM "clients"
          ) sq
    )::text;
END;
$BODY$ LANGUAGE plpgsql STABLE;

To eliminate data from a specific column with a value of

NULL

, you can apply the
column names
condition based on a

CASE

statement.


Solution 2:


The function provided is more concise, readable, and efficient than the initial version. It includes column names but avoids generating curly braces and excessive double quotes.

Assuming that the unused parameter

_session

is part of a larger query or function.

CREATE OR REPLACE FUNCTION load_page(IN _session integer, OUT col1 text, OUT col2 text)
RETURNS SETOF record AS $BODY$
BEGIN
    SELECT string_agg('user_id: ' || user_id ||
                    ', user_name: ' || user_name, ',')
    INTO col1
    FROM "user";
    SELECT string_agg('client_id: ' || client_id ||
                    ', client_name: ' || client_name ||
                    ', client_desc: ' || client_desc, ',')
    INTO col2
    FROM "clients";
    RETURN NEXT;
END;
$BODY$ LANGUAGE plpgsql STABLE;

Frequently Asked Questions

Posted in Sql