How Can I Handle Dynamic SQL Return Types in PostgreSQL with Varying Column Names and Types?

PostgreSQL dynamic SQL and return types

Handling custom return types with different column names and types

In your scenario, the return type will change due to different column names and types. To handle this, you can take advantage of PostgreSQL's ability to return anonymous record types:

<code class="language-sql">CREATE FUNCTION data_of(integer)
RETURNS SETOF record AS
...</code>

However, this approach requires manually specifying column definitions in each function call:

<code class="language-sql">SELECT * FROM data_of(17)
AS foo (colum_name1 integer
      , colum_name2 text
      , colum_name3 real);</code>

To avoid this tedious approach, you can use document data types like JSON or XML to store unstructured data:

<code class="language-sql">CREATE FUNCTION data_of(integer)
RETURNS JSONB AS
...</code>

However, this approach will sacrifice the advantages of PostgreSQL structured data types.

Fixed return type using column conversion

If your data structure is consistent (except for column names), you can return a fixed number of correctly named and typed columns:

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text) AS
$func$
...</code>

For simplicity, each column is explicitly converted to type TEXT.

Variable number of columns with the same type

If you have a variable number of columns of the same type, you can use an array to store the values:

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[]) AS
$func$
...</code>

Also, you can return the column names as an array:

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[]) AS
$func$
...</code>

Polymorphically return all columns of the table

To return all columns of a table, regardless of their structure, you can use the anyelement pseudo data type:

<code class="language-sql">CREATE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

Call this function with NULL of the desired table type to dynamically determine the return type:

<code class="language-sql">SELECT * FROM data_of(NULL::pcdmet, 17);</code>

The above is the detailed content of How Can I Handle Dynamic SQL Return Types in PostgreSQL with Varying Column Names and Types?. For more information, please follow other related articles on the PHP Chinese website!