在RETURNING语句中使用Record as Function参数

我有一个plpgsql函数,它以记录作为参数。

I would like to call this function in the RETURNING statement of an insert, but am unsure what to put as the argument (* does not work) i.e.,

-- Postgres 12

INSERT INTO some_table (a, b, c)
VALUES .....
RETURNING
function_that_takes_record_argument(<the_new_record>)

What can I use in place of <the_new_record>?

评论
  • 苍涼
    苍涼 回复

    要将记录/行传递给函数,您需要明确指定记录类型:

    create table t(a int, b text);
    
    create or replace function f(r record) returns int language plpgsql as $$
    begin
        return r.a;
    end $$;
    
    insert into t values(2, 'b');
    
    select f(t.*), f(t), f((a,b)::t) from t;
    
     f | f | f 
    ---+---+---
     2 | 2 | 2
    
    insert into t values(3, 'c') returning f(t);
    
     f 
    ---
     3
    
    
  • Oo晓oO
    Oo晓oO 回复

    我不确定为什么您的示例不起作用,但是我会尝试修改数据的CTE:

    WITH insert_result AS (
        INSERT INTO ... RETURNING * 
    )
    SELECT your_function_here(insert_result.*)
    

    See more at https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING