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

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

Linda Hamilton
Linda HamiltonOriginal
2025-01-22 23:37:10304browse

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:

<code class="language-sql">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$;</code>

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:

<code class="language-sql">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$;</code>

Dynamicly return any table type

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

<code class="language-sql">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$;</code>

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn