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

问题描述

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;

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.

解决方案

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.

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.

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.

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.

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 ALIAS command.

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

  • Table name as a PostgreSQL function parameter

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

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

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

• Details on format() in the manual.

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