如果我有這樣的表：
CREATE TABLE mytable
(
id SERIAL,
content TEXT,
copyofid INTEGER
);
有沒有辦法在單個插入陳述句id中復制？copyofid
我試過了：
INSERT INTO mytable(content, copyofid) VALUES("test", id);
但這似乎不起作用。
uj5u.com熱心網友回復：
您可以使用 找到串行列后面的序列，pg_get_serial_sequence()并使用它來訪問它，currval()以獲取您的INSERT.
CREATE TABLE mytable
(
id SERIAL,
content TEXT,
copyofid INTEGER
);
--this works for a single-record insert
INSERT INTO mytable
(content, copyofid)
VALUES
('test', currval(pg_get_serial_sequence('mytable','id')));
--inserting more, you'll have to handle both columns relying on the sequence
INSERT INTO mytable
( id,
content,
copyofid)
VALUES
( nextval(pg_get_serial_sequence('mytable','id')),
'test3',
currval(pg_get_serial_sequence('mytable','id'))),
( nextval(pg_get_serial_sequence('mytable','id')),
'test4',
currval(pg_get_serial_sequence('mytable','id')));
table mytable;
-- id | content | copyofid
------ --------- ----------
-- 1 | test | 1
-- 2 | test3 | 2
-- 3 | test4 | 3
--(3 rows)
小提琴
uj5u.com熱心網友回復：
解決方案是創建一個觸發器函式，該函式在 mytable 表中插入新行之前觸發，如果條件為 true，則將 NEW.id 復制到 NEW.copyofid 中：
CREATE OR REPLACE FUNCTION before_insert_mytable() RETURN trigger LANGUAGE plpgsql AS $$
BEGIN
IF condition
THEN NEW.copyofid = NEW.id ;
END IF ;
RETURN NEW ;
END ; $$
CREATE OR REPLACE TRIGGER before_insert_mytable BEFORE INSERT ON mytable
FOR EACH ROW EXECUTE FUNCTION before_insert_mytable () ;
條件也可以直接在WHEN觸發器的子句中而不是在函式中宣告：
CREATE OR REPLACE FUNCTION before_insert_mytable() RETURN trigger LANGUAGE plpgsql AS $$
BEGIN
NEW.copyofid = NEW.id ;
RETURN NEW ;
END ; $$
CREATE OR REPLACE TRIGGER before_insert_mytable BEFORE INSERT ON mytable
WHEN condition
FOR EACH ROW EXECUTE FUNCTION before_insert_mytable () ;
看手冊
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/519428.html