postgresql 从 PL/pgSQL 函数返回具有未知列的动态表

Return dynamic table with unknown columns from PL/pgSQL function

提问by Egidi

I need to create a function that checks on a given table if the infowindowfield exists. If it exists the function must return select * from tablebut if it does not, it must return an additional idfield:

我需要创建一个函数来检查给定的表是否infowindow存在该字段。如果它存在，函数必须返回，select * from table但如果不存在，它必须返回一个附加id字段：

CREATE OR REPLACE FUNCTION getxo_ocx_cincu_preparar_infowindow(
                                              guretabla character varying)
  RETURNS TABLE AS
$BODY$ 
DECLARE
    tabla ALIAS FOR ;

BEGIN

IF  EXISTS (SELECT 1
   FROM   pg_namespace n
   JOIN   pg_class     c ON c.relnamespace = n.oid
   JOIN   pg_attribute a ON a.attrelid = c.oid 
   WHERE  n.nspname = current_schema()  -- default to current schema
   AND    c.relname = tabla
   AND    a.attname = 'infowindow'
   AND    NOT a.attisdropped)
THEN
    RETURN QUERY EXECUTE 'SELECT * from ' ||tabla ;
ELSE
    RETURN QUERY EXECUTE 'SELECT *, ID:' || id::text ||' as infowindow
                                   from ' ||tabla ;
END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

If I use RETURNS SETOF RECORDS, when I do the select to the function I need to specify the columns, which I don't know. And if I use RETURNS TABLEI need to specify the fields too, so I don't know how to do it.

如果我使用RETURNS SETOF RECORDS，当我对函数进行选择时，我需要指定我不知道的列。如果我使用RETURNS TABLE我也需要指定字段，所以我不知道该怎么做。

回答by Erwin Brandstetter

This is hard to solve, because SQL demands to know the return type at call time.
Also, a plpgsql function needs to have a well defined return type.

这很难解决，因为 SQL 要求在调用时知道返回类型。
此外，plpgsql 函数需要具有明确定义的返回类型。

If you choose to return anonymous records, you get what you defined: anonymous records. Postgres does not know what's inside. Therefore, a column definition list is requiredto decompose the type.

如果您选择返回匿名记录，您将得到您定义的内容：匿名记录。Postgres 不知道里面有什么。因此，一个字段定义列表需要分解的类型。

There are various workarounds, depending on exact requirements. If you have any way of knowing the return type at call time, I suggest polymorphic typesas outlined in the last chapter of this answer ("Various complete table types"):
Refactor a PL/pgSQL function to return the output of various SELECT queries

有多种解决方法，具体取决于具体要求。如果您有任何方式知道调用时的返回类型，我建议使用本答案最后一章中概述的多态类型（“各种完整的表类型”）：
重构 PL/pgSQL 函数以返回各种 SELECT 查询的输出

But that does not cover adding another column to the return type at runtime inside the function. That's just not possible. I would rethink the whole approach.

但这并不包括在运行时向函数内部的返回类型添加另一列。那是不可能的。我会重新考虑整个方法。

As for your current approach, the closest thing I can think of would be a temporary table (or a cursor), that you query in a second callwithin a single transaction.

至于你目前的做法，是最接近我能想到的将是一个临时表（或光标），您在查询第二个电话一内单个事务。

You have a couple of other problems in your code. See notes below.

您的代码中还有其他一些问题。请参阅下面的注释。

Proof of concept

概念证明

CREATE OR REPLACE FUNCTION f_tbl_plus_infowindow (_tbl regclass) -- regclass!
  RETURNS void AS  -- no direct return type
$func$
DECLARE
   -- appending _tmp for temp table
   _tmp text := quote_ident(_tbl::text || '_tmp');
BEGIN

-- Create temp table only for duration of transaction
EXECUTE format(
   'CREATE TEMP TABLE %s ON COMMIT DROP AS TABLE %s LIMIT 0', _tmp, _tbl);

IF EXISTS (
   SELECT 1
   FROM   pg_attribute a
   WHERE  a.attrelid = _tbl
   AND    a.attname  = 'infowindow'
   AND    a.attisdropped = FALSE)
THEN
   EXECUTE format('INSERT INTO %s SELECT * FROM %s', _tmp, _tbl);
ELSE
  -- This is assuming a NOT NULL column named "id"!
   EXECUTE format($x$
      ALTER  TABLE %1$s ADD COLUMN infowindow text;
      INSERT INTO %1$s
      SELECT *, 'ID: ' || id::text
      FROM   %2$s $x$
     ,_tmp, _tbl);
END IF;

END
$func$ LANGUAGE plpgsql;

The call has to be in a single transaction. You may have to start an explicit transaction, depending on your client.

调用必须在单个事务中。您可能必须启动显式事务，具体取决于您的客户。

BEGIN;
SELECT f_tbl_plus_infowindow ('tbl');
SELECT * FROM tbl_tmp;  -- do something with the returned rows
ROLLBACK;               -- or COMMIT, does not matter here

SQL Fiddle.

SQL小提琴。

Alternatively you could let the temporary table live for the duration of the session. Be wary of naming collisions with repeated calls, though.

或者，您可以让临时表在会话期间一直存在。但是，请注意重复调用时的命名冲突。

Notes

笔记

• Use parameter names instead of the outdated ALIAScommand.

• To actually "default" to the current schema, use the simpler query I display. Using regclassdoes the trick automatically. Details:

  • Table name as a PostgreSQL function parameter

  In addition, this also avoids syntax errors and possible SQL injectionfrom non-standard (or maliciously malformed) table names in your original code.

• The code in your ELSEclause wouldn't work at all.

• TABLE tbl;is basically short for SELECT * FROM tbl;.

• Details on format()in the manual.

• 使用参数名称而不是过时的ALIAS命令。

• 要实际“默认”为当前模式，请使用我显示的更简单的查询。使用regclass会自动解决问题。细节：

  • 表名作为 PostgreSQL 函数参数

  此外，这也避免了原始代码中的非标准（或恶意格式错误）表名的语法错误和可能的SQL 注入。

• 您ELSE子句中的代码根本不起作用。

• TABLE tbl;基本上是SELECT * FROM tbl;.

• format()手册中有详细说明。