How to make a function return a dynamically named column?

Question

Simple pseudocode example:

create function myfunc (someparam text, desired_return_column_name text)
returns table (somekeyname int, MAGIC_DYNAMIC_NAME text)
language SQL
as $funcdef$
  select
    somekeyname,
    fieldofinterest as <MAGIC HERE?> desired_return_column_name
  from the_base_table
  where some_other_field = someparam
$funcdef$;

In English, how can I make an SQL function in Postgres (9.6) return a table with statically defined types but where one of the returned column names is named according to a parameter passed in?

If this is not possible with an SQL function, is it possible with a PL/pgSQL function?

Daniel Vérité · Accepted Answer · 2017-12-21T18:57:55.787

No it's not possible in functions, independently of their language. The structure of a function result set is always static, just like the result set of any given query. In fact it's related, as this structure (column names and types) must be incorporated into the SQL query that calls the function.

Remember that a client application must be able to prepare a query and obtain a description of its results before executing it. Also a prepared query can be repeateadly executed with different parameters, and cannot return a different structure across executions. It would be impossible to guarantee that property if a function could independantly and dynamically decide what structure it wants to return.

I'm not sure whether the documentation makes that point explicitly somewhere (of course it tends to describe what can be done, not what is impossible) but here are some bits in the protocol flow documentation that support this answer:

The Describe message (portal variant) specifies the name of an existing portal (or an empty string for the unnamed portal). The response is a RowDescription message describing the rows that will be returned by executing the portal; or a NoData message if the portal does not contain a query that will return rows; or ErrorResponse if there is no such portal.

In future versions (PostgreSQL 11 or beyond), we might have stored procedures, which are not called through a SQL query but through a special CALL statement that is not tied to a specific structure of result set. In fact, a stored procedure should be able to return several result sets with different structures, without prior declaration of the return types. This is currently being worked on and discussed in the -hackers mailing list (see Dynamic result sets from procedures and related recent threads on SQL procedures).

But as of PostgreSQL 10, there is no way to practically get dynamic result sets outside of using encapsulating types like JSON or XML.

How to make a function return a dynamically named column?

1 Answers1