通过"*"行扩展这里不支持 [英] Row expansion via "*" is not supported here

问题描述

我处于触发环境中，并试图使以下代码段起作用.

I'm in a trigger context and trying to get following snippet to work.

execute format('insert into %I (user_name, action, new_values, query) 
    values (''%I'', ''i'', hstore(($1).*), current_query())', 
     tg_table_name::text || '_audit', current_user::text)
using new;

我遇到以下错误

[SQL]insert into book (title, n_pages) values ('PG is Great', 250);

[Err] ERROR:  row expansion via "*" is not supported here
LINE 2: values ('u1', 'i', hstore(($1).*), current_q...
                                               ^
QUERY:  insert into book_audit (user_name, action, new_values, query) 
        values ('u1', 'i', hstore(($1).*), current_query())
CONTEXT:  PL/pgSQL function "if_modified_func" line 8 at EXECUTE statement

这里不支持有关通过"*"修复行扩展的任何建议? 不能选择耦合到特定的模式.

Any suggestion on how to fix row expansion via "*" is not supported here ? Coupling to specific schema is not an option.

推荐答案

从我的头顶开始，它应该像这样:

From the top of my head, it should work like this:

EXECUTE format('
   INSERT INTO %I (user_name, action, new_values, query) 
   SELECT $1, ''i'', $2, current_query()'
   , tg_table_name::text || '_audit')
USING current_user, hstore(NEW);

最好在USING子句中提供所有值.
而且，您可以直接通过hstore(record)将记录发送到hstore .

It is better to supply all values with the USING clause.
And you can cast a record to hstore directly with hstore(record).

这篇关于通过"*"行扩展这里不支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！