plpgsql函数:从根据随机表创建的视图返回行 [英] plpgsql function: Return rows from a view created from random table

问题描述

我想创建一个函数，该函数从一个未知表创建的视图中返回行:

I want to create a function that returns rows from a view created from a unknown table(s):

    CREATE OR REPLACE FUNCTION tt_query(text, timestamp without time zone)
      RETURNS SETOF record AS
    $$
    DECLARE

    orig_name ALIAS FOR $1;
    data_tt ALIAS FOR $2;

    BEGIN

    [...]

    EXECUTE 'create OR REPLACE TEMP view temp as 
    select * 
    from  '
    ||orig_name 
    ||' where trigger_changed >'
    ||quote_literal(data_tt)
    ||' ORDER BY trigger_changed DESC';

    [...]--other work on view temp

    --NOW I WANT RETURN THE ROW OF view temp
    END;
    $$
    LANGUAGE plpgsql VOLATILE

好吧，我已经想到(在您的帮助下):

ok I have think (with yours help) this:

表格:

create table t(a integer, b text);

功能:

CREATE OR REPLACE FUNCTION f()
  RETURNS SETOF record AS
$$
BEGIN

RETURN QUERY EXECUTE 'SELECT * FROM t';

END;
  $$
  LANGUAGE plpgsql VOLATILE

类型:

CREATE TYPE y AS (
    a int,
    b text
);

现在有可能吗?:

select * from f() as y;

在我的情况下，

y名称是一个变量，我在另一个函数中创建了它

y name in my case is a variable an I create it in an other function

推荐答案

它可以像这样工作:

CREATE OR REPLACE FUNCTION tt_query(orig_name regclass, data_tt timestamp)
  RETURNS SETOF record AS
$func$
BEGIN

EXECUTE 'CREATE OR REPLACE TEMP VIEW tmp as 
select * 
from  '
|| orig_name 
|| ' where trigger_changed >'
|| quote_literal(data_tt)
|| ' ORDER BY trigger_changed DESC';

-- other work on view tmp

-- return the rows of view temp
RETURN QUERY
SELECT * FROM tmp;

END
$func$  LANGUAGE plpgsql;

• 请注意对象标识符类型 以自动避免SQL注入.

  • Note the use of the object identifier type regclass to automatically avoid SQL injection.

    如果不需要，请不要使用过时的语法var ALIAS for $1.改为声明参数名称.

    Do not use the outdated syntax var ALIAS for $1 if you don't have to. Declare parameter names instead.

    即使允许，我也不会使用关键字temp作为标识符.改为使用tmp.

    I wouldn't use the keyword temp as identifier, even if that is allowed. Using tmp instead.

    使用RETURN QUERY返回一组记录.这甚至可以是不带EXECUTE的静态调用.但是，您返回的是匿名记录，Postgres每次调用都需要一个列定义列表:

    Use RETURN QUERY to return a set of records. This can even be a static call without EXECUTE. However, you are returning anonymous records and Postgres demands a column definition list with every call:

    SELECT * FROM tt_query('tbl_name', '2014-02-15 12:00')
    AS f(col1 int, col2 text, ...);

    这很笨拙.

    如果您知道返回类型(即使表名正在更改，那么这些列的列表也可能共享相同的类型)，请在创建时进行声明.考虑以下相关问题:
    PostgreSQL:错误: 42601:返回记录"

    If you know the return type (even if table names are changing, there list of columns may share the same types), declare it at creation time. Consider this related question:
    PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

    如果返回类型有所不同(与提供的表名不同)，仍然有更好的解决方案.由于要使用SELECT * FROM tbl创建视图，因此可以将表本身的众所周知类型用作

    If the return type varies with the the provided table name, there is still a much better solution. Since you are creating a view with SELECT * FROM tbl, you can utilize the well-known type of the table itself as polymorphic parameter:

    CREATE OR REPLACE FUNCTION tt_query(orig_name anyelement, data_tt timestamp)
      RETURNS SETOF anyelement AS
    $func$
    BEGIN
    
    EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS
       SELECT * FROM  %s
       WHERE  trigger_changed > %L
       ORDER  BY trigger_changed DESC'
      ,pg_typeof(orig_name)
      ,data_tt);
    
    -- other work on view tmp
    
    -- return the rows of view tmp
    RETURN QUERY
    SELECT * FROM tmp;
    
    END
    $func$  LANGUAGE plpgsql;
    

    简化呼叫:

    SELECT * FROM tt_query(NULL::tbl_name, '2014-02-15 12:00');
    

    还使用 format() 为了安全&简单的字符串连接.

    Also using format() for safe & simple string concatenation.

    此相关答案中的更多详细信息:
    重构a PL/pgSQL函数返回各种SELECT查询的输出

    More details in this related answer:
    Refactor a PL/pgSQL function to return the output of various SELECT queries

    这篇关于plpgsql函数:从根据随机表创建的视图返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！