更新 PL/pgSQL 中的一整行 [英] UPDATE a whole row in PL/pgSQL
问题描述
我有plpgsql函数:
I have plpgsql function:
CREATE OR REPLACE FUNCTION test() RETURNS VOID AS
$$
DECLARE
my_row my_table%ROWTYPE;
BEGIN
SELECT * INTO my_row FROM my_table WHERE id='1';
my_row.date := now();
END;
$$ LANGUAGE plpgsql;
我想知道是否可以直接更新 my_row
记录.
I would like to know if it's possible to directly UPDATE my_row
record.
我现在发现的唯一方法是:
The only way I've found to do it now is:
UPDATE my_table SET date=now() WHERE id='1';
请注意,这只是一个示例函数,真正的函数远比这复杂.
Note this is only an example function, the real one is far more complex than this.
我使用的是 PostgreSQL 9.2.
I'm using PostgreSQL 9.2.
更新:
对不起,我想说的是:
SELECT * INTO my_row FROM my_table INTO my_row WHERE id='1';
make_lots_of_complicated_modifications_to(my_row, other_complex_parameters);
UPDATE my_row;
即使用 my_row
将信息持久保存在基础表中.我有很多参数要更新.
I.e. Use my_row
to persist information in the underlying table. I have lots of parameters to update.
推荐答案
我想知道是否可以直接更新my_row"记录.
I would like to know if it's possible to directly update "my_row" record.
是的.
您可以在 plpgsql 中更新行或记录类型的列 - 就像您拥有它一样.显然它应该可以工作?
It is.
You can update columns of a row or record type in plpgsql - just like you have it. It should be working, obviously?
这当然会更新基础表,而不是变量!
This would update the underlying table, of course, not the variable!
UPDATE my_table SET date=now() WHERE id='1';
你在这里混淆了两件事......
You are confusing two things here ...
我认为 PostgreSQL 中没有可以 UPDATE
整行的语法.您可以UPDATE
列列表,不过.考虑这个演示:
I don't think there is syntax in PostgreSQL that can UPDATE
a whole row. You can UPDATE
a column list, though. Consider this demo:
注意我如何使用 thedate
而不是 date
作为列名,date
是一个 每个 SQL 标准中的保留字 和 PostgreSQL 中的类型名称.
Note how I use thedate
instead of date
as column name, date
is a reserved word in every SQL standard and a type name in PostgreSQL.
CREATE TEMP TABLE my_table (id serial, thedate date);
INSERT INTO my_table(thedate) VALUES (now());
CREATE OR REPLACE FUNCTION test_up()
RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
_r my_table;
BEGIN
SELECT * INTO _r FROM my_table WHERE id = 1;
_r.thedate := now()::date + 5 ;
UPDATE my_table t
-- explicit list of columns to be to updated
SET (id, thedate) = (_r.id, _r.thedate)
WHERE t.id = 1;
END
$func$;
SELECT test_up();
SELECT * FROM my_table;
但是,您可以INSERT
整行 轻松.只是不要为表格提供列列表(您通常应该这样做,但在这种情况下完全可以,不要这样做).
However, you can INSERT
a whole row easily. Just don't supply a column list for the table (which you normally should, but in this case it is perfectly ok, not to).
作为一个 UPDATE
在内部是一个 DELETE
后跟一个 INSERT
无论如何,一个函数会自动将所有内容封装在一个事务中,我不'看不出来,为什么你不能改用这个:
As an UPDATE
is internally a DELETE
followed by an INSERT
anyway, and a function automatically encapsulates everything in a transaction, I don't see, why you couldn't use this instead:
CREATE OR REPLACE FUNCTION x.test_ delins()
RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
_r my_table;
BEGIN
SELECT * INTO _r
FROM my_table WHERE id = 1;
_r.thedate := now()::date + 10;
DELETE FROM my_table t WHERE t.id = 1;
INSERT INTO my_table SELECT _r.*;
END
$func$;
这篇关于更新 PL/pgSQL 中的一整行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!