PostgreSQL在触发功能中动态修改NEW记录中的字段 [英] PostgreSQL modifying fields dynamically in NEW record in a trigger function
问题描述
我有一个包含ID和用户名(以及其他详细信息)的用户表，以及其他几个引用此表的表，这些表具有不同的列名(CONSTRAINT some_name FOREIGN KEY (columnname) REFERENCES "user" (userid)).我需要做的是将用户名添加到引用表中(为删除整个用户表做准备).当然，只需使用一个ALTER TABLE和UPDATE即可轻松完成此操作，并且(使用公平的方式)使这些触发器保持最新也是很容易的.但这是触发功能使我有些烦恼.我可以为每个表使用单独的函数，但这似乎是多余的，因此我为此目的创建了一个通用函数:
I have a user table with IDs and usernames (and other details) and several other tables referring to this table with various column names (CONSTRAINT some_name FOREIGN KEY (columnname) REFERENCES "user" (userid)). What I need to do is add the usernames to the referring tables (in preparation for dropping the whole user table). This is of course easily accomplished with a single ALTER TABLE and UPDATE, and keeping these up-to-date with triggers is also (fairly) easy. But it's the trigger function that is causing me some annoyance. I could have used individual functions for each table, but this seemed redundant, so I created one common function for this purpose:
CREATE OR REPLACE FUNCTION public.add_username() RETURNS trigger AS
$BODY$
DECLARE
sourcefield text;
targetfield text;
username text;
existing text;
BEGIN
IF (TG_NARGS != 2) THEN
RAISE EXCEPTION 'Need source field and target field parameters';
END IF;
sourcefield = TG_ARGV[0];
targetfield = TG_ARGV[1];
EXECUTE 'SELECT username FROM "user" WHERE userid = ($1).' || sourcefield INTO username USING NEW;
EXECUTE format('SELECT ($1).%I', targetfield) INTO existing USING NEW;
IF ((TG_OP = 'INSERT' AND existing IS NULL) OR (TG_OP = 'UPDATE' AND (existing IS NULL OR username != existing))) THEN
CASE targetfield
WHEN 'username' THEN
NEW.username := username;
WHEN 'modifiername' THEN
NEW.modifiername := username;
WHEN 'creatorname' THEN
NEW.creatorname := username;
.....
END CASE;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
并使用触发功能:
CREATE TRIGGER some_trigger_name BEFORE UPDATE OR INSERT ON my_schema.my_table FOR EACH ROW EXECUTE PROCEDURE public.add_username('userid', 'username');
这是触发函数的工作方式，它通过TG_ARGV接收原始的源字段名称(例如userid)和目标字段名称(username).然后使用这些信息来填充(可能)缺少的信息.所有这些工作都足够好，但是如何摆脱CASE -mess?如果我事先不知道字段名称(或者可能有很多事情)，是否可以动态修改NEW记录中的值?它在targetfield参数中，但是显然NEW.targetfield不起作用，也不是NEW[targetfield]之类的东西(例如Javascript).
The way this works is the trigger function receives the original source field name (for example userid) and the target field name (username) via TG_ARGV. These are then used to fill in the (possibly) missing information. All this works nice enough, but how can I get rid of that CASE-mess? Is there a way to dynamically modify the values in the NEW record when I don't know the name of the field in advance (or rather it can be a lot of things)? It is in the targetfield parameter, but obviously NEW.targetfield does not work, nor something like NEW[targetfield] (like Javascript for example).
有什么想法可以做到这一点吗?除了使用PL/Python等.
Any ideas how this could be accomplished? Besides using for instance PL/Python..
推荐答案
没有基于plpgsql的简单解决方案.一些可能的解决方案:
There are not simple plpgsql based solutions. Some possible solutions:
- 使用
hstore扩展名.
CREATE TYPE footype AS (a int, b int, c int);
postgres=# select row(10,20,30);
row
------------
(10,20,30)
(1 row)
postgres=# select row(10,20,30)::footype #= 'b=>100';
?column?
-------------
(10,100,30)
(1 row)
基于
hstore的功能可以非常简单:
hstore based function can be very simple:
create or replace function update_fields(r anyelement,
variadic changes text[])
returns anyelement as $$
select $1 #= hstore($2);
$$ language sql;
postgres=# select *
from update_fields(row(10,20,30)::footype,
'b', '1000', 'c', '800');
a | b | c
----+------+-----
10 | 1000 | 800
(1 row)
- 几年前，我写了一个扩展 pl工具箱.有一个功能
record_set_fields:
- Some years ago I wrote a extension pl toolbox. There is a function
record_set_fields:
pavel=# select * from pst.record_expand(pst.record_set_fields(row(10,20),'f1',33));
name | value | typ
------+-------+---------
f1 | 33 | integer
f2 | 20 | integer
(2 rows)
可能您会找到一些基于plpgsql的解决方案，这些解决方案是基于系统表和数组的一些技巧，如
Probably you can find some plpgsql only solutions based on some tricks with system tables and arrays like this, but I cannot to suggest it. It is too less readable and for not advanced user just only black magic. hstore is simple and almost everywhere so it should be preferred way.
在PostgreSQL 9.4(也许是9.3)上，您可以尝试通过JSON操作来变魔术:
On PostgreSQL 9.4 (maybe 9.3) you can try to black magic with JSON manipulations:
postgres=# select json_populate_record(NULL::footype, jo)
from (select json_object(array_agg(key),
array_agg(case key when 'b'
then 1000::text
else value
end)) jo
from json_each_text(row_to_json(row(10,20,30)::footype))) x;
json_populate_record
----------------------
(10,1000,30)
(1 row)
因此我能够编写函数:
CREATE OR REPLACE FUNCTION public.update_field(r anyelement,
fn text, val text,
OUT result anyelement)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
declare jo json;
begin
jo := (select json_object(array_agg(key),
array_agg(case key when 'b' then val
else value end))
from json_each_text(row_to_json(r)));
result := json_populate_record(r, jo);
end;
$function$
postgres=# select * from update_field(row(10,20,30)::footype, 'b', '1000');
a | b | c
----+------+----
10 | 1000 | 30
(1 row)
基于JSON的功能应该不会太快. hstore应该更快.
JSON based function should not be terrible fast. hstore should be faster.
这篇关于PostgreSQL在触发功能中动态修改NEW记录中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
