SQL 带参数的Postgresql触发器函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7726237/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql trigger function with parameters
提问by Arsen7
I want to create a trigger on a table called takesin postgresql to update a value in another table called studentI'm trying to do it in the following way. But I'm getting an error that there is syntax error near "OLD". I don't understand whats wrong with this. This is my code:
我想创建一个触发器上称为表需要在PostgreSQL中更新另一个表称为价值的学生，我试图做到这一点通过以下方式。但是我收到一个错误，提示“OLD”附近存在语法错误。我不明白这有什么问题。这是我的代码：
CREATE OR REPLACE FUNCTION upd8_cred_func
(id1 VARCHAR, gr1 VARCHAR,id2 VARCHAR, gr2 VARCHAR)
RETURNS void AS $$
BEGIN
IF (id1=id2 and gr1 is null and gr2 is not null) THEN
update student set tot_cred = tot_cred + 6 where id = id1;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER upd8_cred
AFTER UPDATE ON takes
FOR EACH ROW
EXECUTE PROCEDURE upd8_cred_func(OLD.id,OLD.grade,NEW.id,NEW.grade);
回答by Arsen7
You do not need to pass the NEW and OLD as parameters to the trigger function. They are automagicallyavailable there:
您不需要将 NEW 和 OLD 作为参数传递给触发器函数。它们在那里自动可用：
http://www.postgresql.org/docs/9.1/interactive/trigger-definition.html:
http://www.postgresql.org/docs/9.1/interactive/trigger-definition.html：
The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)
触发器函数必须声明为不带参数并返回类型触发器的函数。（触发器函数通过专门传递的 TriggerData 结构接收其输入，而不是以普通函数参数的形式。）
About the records passed to the trigger procedure, please see http://www.postgresql.org/docs/9.1/interactive/plpgsql-trigger.html:
关于传递给触发器过程的记录，请参见http://www.postgresql.org/docs/9.1/interactive/plpgsql-trigger.html：
When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are: [...] NEW, [...] OLD [...]
当调用 PL/pgSQL 函数作为触发器时，会在顶级块中自动创建几个特殊变量。它们是：[...] 新的，[...] 旧的 [...]
As SeldomNeedypointed in the comment below, you can still pass and use parameters to the trigger function. You declarethe function as taking no parameters, but when definingthe trigger (by CREATE TRIGGER), you may add some.
正如SeldomNeedy在下面的评论中指出的那样，您仍然可以将参数传递和使用到触发器函数。您将函数声明为不带参数，但在定义触发器 (by CREATE TRIGGER) 时，您可以添加一些参数。
They will be available for the trigger as TG_NARG(the number of such parameters), and TG_ARGV[](an array of textvalues).
它们将可用于触发器TG_NARG（此类参数的数量）和TG_ARGV[]（文本值数组）。
回答by SeldomNeedy
As Gregstated, trigger functions can take arguments, but the functions themselves cannot have declared parameters. Here's a simple example in plpgsql:
正如Greg所说，触发器函数可以带参数，但函数本身不能有声明的参数。这是 plpgsql 中的一个简单示例：
CREATE TABLE my_table ( ID SERIAL PRIMARY KEY ); -- onelined for compactness
CREATE OR REPLACE FUNCTION raise_a_notice() RETURNS TRIGGER AS
$$
DECLARE
arg TEXT;
BEGIN
FOREACH arg IN ARRAY TG_ARGV LOOP
RAISE NOTICE 'Why would you pass in ''%''?',arg;
END LOOP;
RETURN NEW; -- in plpgsql you must return OLD, NEW, or another record of table's type
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER no_inserts_without_notices BEFORE INSERT ON my_table
FOR EACH ROW EXECUTE PROCEDURE raise_a_notice('spoiled fish','stunned parrots');
INSERT INTO my_table DEFAULT VALUES;
-- the above kicks out the following:
--
-- NOTICE: Why would you pass in 'spoiled fish'?
-- NOTICE: Why would you pass in 'stunned parrots'?
--
There are a few other goodies such as TG_NARGS(to know how many args you got without looping through them) discussed in the docs. There's also information there about how to get the name of the triggering table in case you have mostly-but-not-quite-shared logic for one trigger-function that spans a number of tables.
还有一些其他的好东西，例如TG_NARGS（在不遍历它们的情况下知道你得到了多少参数）在docs 中讨论过。那里还有关于如何获取触发表名称的信息，以防万一您有一个跨越多个表的触发器函数的大部分但不完全共享的逻辑。
回答by Greg
The trigger function canhave parameters, but, you can't have those parameters passed like a normal function (e.g. arguments in the function definition). You can get the same result... In python you get access to the OLD and NEW data as the answer above describes. For example, I can use TD['new']['column_name'] in python to reference the new data for column_name. You also have access to the special variable TD['args']. So, if you like:
触发器函数可以有参数，但是，您不能像普通函数一样传递这些参数（例如，函数定义中的参数）。您可以获得相同的结果......在python中，您可以访问上述答案所描述的旧数据和新数据。例如，我可以在 python 中使用 TD['new']['column_name'] 来引用 column_name 的新数据。您还可以访问特殊变量 TD['args']。所以，如果你喜欢：
create function te() returns trigger language plpython2u as $function$
plpy.log("argument passed 1:%s 2:%s" %(TD['args'][0], TD['args'][1], ))
$function$
create constraint trigger ta after update of ttable
for each for execute procedure te('myarg1','myarg2');
Granted, these arguments are static, but, they are useful when calling a common trigger function from multiple trigger declarations. I am pretty sure that the same variables are available for other stored procedure languages. (sorry if the code doesn't work verbatim, but, I do practice this technique, so I know you can pass arguments!).
诚然，这些参数是静态的，但是在从多个触发器声明调用公共触发器函数时它们很有用。我很确定相同的变量可用于其他存储过程语言。（对不起，如果代码不能逐字运行，但是，我确实练习了这种技术，所以我知道您可以传递参数！）。
