How Can I Modify a PL/pgSQL Function to Return the Results of a Dynamic SQL Query?

Modify PL/pgSQL function to return query results

Background

Initially, you create a function that outputs a well-formed PostgreSQL SELECT query string. Now you want to execute the generated SELECT statement directly against the database and retrieve its results.

Methods: Dynamic SQL and Return Types

Dynamic SQL

To execute dynamic SQL, we use the EXECUTE command. Since you are not using a cursor, you can use the RETURN QUERY EXECUTE syntax.

Return Type

The challenge is returning records of undefined type since the function needs to declare the return type. We will consider various approaches depending on the specific characteristics of the data.

Simple solution using fixed return type

Assuming fixed return types for timestamp, text, and text columns, we can define the function as follows:

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text)
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora, %s
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _id;
END
$$ LANGUAGE plpgsql;</code>

Variable number of columns of the same type

If you have a variable number of columns, and all columns are of the same type (e.g. double), we can use the ARRAY type to nest the values:

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
AS $$
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
$$ LANGUAGE plpgsql;</code>

A variety of complete table types

To return all columns of the table, we can use polymorphic types:

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

Note: In the second example, the string_to_array function requires a delimiter, and , is added here as the delimiter. The third example uses the %I formatting identifier to handle table names in a safer way to prevent SQL injection. Which method you choose depends on your specific needs and data structure. If possible, providing more information about your table structure and desired results can help me provide a more precise and efficient solution.

The above is the detailed content of How Can I Modify a PL/pgSQL Function to Return the Results of a Dynamic SQL Query?. For more information, please follow other related articles on the PHP Chinese website!