How Can I Refactor a PL/pgSQL Function to Return Results from Dynamic SELECT Queries with Variable Column Numbers and Types?

Refactor PL/pgSQL functions to return output of various SELECT queries

Introduction

You have a PL/pgSQL function that generates a SELECT query as a text string. Now you want to further enhance this function to execute the generated query and return actual results, similar to the behavior of a standalone query.

Variable return type

The challenge is that the structure and type of data returned may vary depending on the underlying table being queried. A simple solution is to define a fixed return type with a common column name and cast all values ​​to text, as in the modified function like this:

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = 
      ORDER  BY datahora'
   USING  _id;
END
$func$;

Handling variable number of columns

If the number of columns returned changes, but the types remain the same (e.g. all are float8), you can use an array of nested values:

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[])
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT datahora
           , string_to_array()  -- AS names
           , ARRAY[%s]            -- AS values
      FROM   %s
      WHERE  id = 
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$;

Dynamicly return any table type

To handle various complete table types with different structures, you can take advantage of polymorphic types:

CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql 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$;

This function requires that you pass a NULL value that is cast to the target table type (for example, NULL::pcdmet). It then automatically determines the appropriate row type and returns the individual columns when used with SELECT * FROM data_of().

The above is the detailed content of How Can I Refactor a PL/pgSQL Function to Return Results from Dynamic SELECT Queries with Variable Column Numbers and Types?. For more information, please follow other related articles on the PHP Chinese website!