从PL/pgSQL函数返回具有未知列的动态表 [英] Return dynamic table with unknown columns from PL/pgSQL function

问题描述

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

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

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

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;

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

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 TABLE I need to specify the fields too, so I don't know how to do it.

推荐答案

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

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.

如果您选择返回匿名记录，您将获得定义的内容:匿名记录. Postgres不知道里面有什么.因此，需要列定义列表来分解类型.

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 required to decompose the type.

有多种解决方法，具体取决于确切的要求.如果您有某种方法在调用时知道返回类型 ，我建议使用多态类型，如该答案最后一章所述(各种完整表类型"):
重构a PL/pgSQL函数返回各种SELECT查询的输出

There are various workarounds, depending on exact requirements. If you have any way of knowing the return type at call time, I suggest polymorphic types as 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

但这并不涉及在运行时在函数内的返回类型中添加另一列.那是不可能的.我会重新考虑整个方法.

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 call within a single transaction.

您的代码中还有几个其他问题.请参阅下面的注释.

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

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小提琴.

SQL Fiddle.

或者，您可以让临时表在会话期间保持活动状态.但是，请注意不要为重复调用而命名冲突.

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

• 使用参数名称代替过时的ALIAS命令.

要实际默认"为当前架构，请使用我显示的更简单的查询.使用regclass可以自动完成操作.详细信息:

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

• 表名作为PostgreSQL函数参数

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

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

您的ELSE子句中的代码根本无法工作.

The code in your ELSE clause wouldn't work at all.

TABLE tbl;基本上是SELECT * FROM tbl;的缩写.

format()上的详细信息在手册中.

Details on format() in the manual.

这篇关于从PL/pgSQL函数返回具有未知列的动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！