How Can a PL/pgSQL Function Dynamically Return Results from Various SELECT Queries?

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

Dynamic SQL and return types

Returns records of undefined type

The original function declared a return type of text, but now the goal is to execute the generated SQL statement and return the result as a record. To do this you need to use dynamic SQL and deal with returning records of a type that is not yet defined.

Custom types with numeric and data type mutability

One approach is to create a custom return type with a fixed number of columns but a variable data type. However, this requires defining a list of column definitions for each call, which becomes impractical when the column names and data types are not known in advance.

Use polymorphic types

pg_typeof() Returns the table name as the object identifier type (regtype). When automatically converted to text, identifiers are automatically double-quoted and pattern-qualified, thus preventing SQL injection.

RETURN QUERY EXECUTE

The preferred way to execute dynamic SQL and return rows is RETURN QUERY EXECUTE. It allows you to execute SQL queries and return the results as rowsets with well-defined row types.

Code Example

Suppose you want to return all columns of any table based on the table type parameter:

<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  -- 自动用双引号括起来并进行模式限定
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

Usage

Call this function with the table type as the first parameter and the ID as the second parameter. This function will return all columns of the record with the given ID in the specified table.

<code class="language-sql">SELECT * FROM data_of(NULL::pcdmet, 17);
-- 将pcdmet替换为任何其他表名。</code>

The above is the detailed content of How Can a PL/pgSQL Function Dynamically Return Results from Various SELECT Queries?. For more information, please follow other related articles on the PHP Chinese website!