防止顯式插入串列列

我想防止顯式插入串列列。我想出了以下觸發器：

drop table test_table;

create table test_table(
id bigserial primary key,
foobar text
);

create or replace function serial_id_check() returns trigger as
$$
begin
   if new.id != currval(TG_TABLE_NAME||'_id_seq') then
       raise exception 'Explicit insert into serial id, currval = %, tried to insert = %', currval(TG_TABLE_NAME||'_id_seq'), new.id;
   end if;
   return new;
end;
$$ language plpgsql;

create trigger test_table_serial_id_check
before insert on test_table
for each row
execute procedure serial_id_check();

也許有更好的方法？也許這種方法被打破了，這根本無法實現？

PS我還考慮不授予插入和更新的權限，而僅授予用於插入/更新的pgplsql過程-但這種方法現在對我來說是不可能的。

在Postgres 10或更高版本中，請考慮使用IDENTITY列。喜歡：

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

看：

• 什麼是身份列？
• 自動遞增表列

---

舊版本的原始答案：

可更新視圖

在**Postgres 9.4中，**每列的視圖是自動更新的。即，只要滿足基本條件，列就會自動更新，以便對底層列進行簡單引用。表達式不是。我們可以利用這個特性：

CREATE TABLE test_table(
 id serial PRIMARY KEY
, foobar text
);

CREATE VIEW test_view AS
SELECT id * 1 AS id  -- id unchanged but not updatable!
    , foobar
FROM   test_table;

乘以id，1值不變，但列不再自動更新。

• REVOKE INSERT/UPDATE權限test_table來自您的使用者。
• 讓擁有這些權限的另一個角色擁有該視圖。
• GRANT INSERT/UPDATE給test_view你的使用者。

現在他們可以做任何事情，但他們不能手動設置或更改id. 如何處理是你的選擇DELETE。

這個簡單快速的解決方案在 Postgres 9.4 中開箱即用。Postgres 9.3 引入了可自動更新的視圖，但該版本中的所有列都需要可更新才能使該功能正常工作。

在Postgres 9.3 或更早版本中，提供INSTEAD OF INSERT觸發器或無條件ON INSERT DO INSTEAD規則。

手動編寫規則/觸發器時，您不需要id * 1技巧。您甚至可能希望在 Postgres 9.4+ 中使用它來微調功能。例子：

CREATE VIEW test_view1 AS TABLE test_table;

CREATE OR REPLACE RULE ins_up AS
ON INSERT TO test_view1 DO INSTEAD
INSERT INTO test_table (foobar) VALUES (NEW.foobar);

現在，在視圖上是可能的，INSERT但還不是。如果你想寫更多的規則。UPDATE``DELETE

SQL Fiddle (Postgres 9.6)

重要區別：雖然可自動更新的視圖會拒絕嘗試INSERT/UPDATE值，但會id出現異常，而展示的RULE只是忽略值id並繼續進行。

簡單的觸發器

或者，您可以簡單地用序列中的下一個值無條件地覆蓋序列列：

CREATE OR REPLACE FUNCTION force_serial_id()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
  NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_NAME), 'id'));
  RETURN NEW;
END
$func$;

這比試圖變得聰明更簡單、更便宜、更不容易出錯。quote_ident()安全地轉義其他非法名稱（也防止 SQL 注入）。

就像@dezso 評論的那樣，這會在正常操作中每行燃燒兩個數字，因為在觸發函式啟動**之前serial會獲取預設值。通常，序列中的間隙應該不是問題（無論如何都是可以預料的），但是您可以通過從列中刪除來避免副作用。然後你完全依賴觸發器。DEFAULT

UPDATE您可以使用單獨的觸發器和触發器本身的條件來微調案例WHEN (OLD.id <> NEW.id)。語法範例：

• 如何防止 PostgreSQL 觸發器被另一個觸發器觸發？

請注意使用pg_get_serial_sequence()，它不會像您原來的非基本標識符那樣中斷。想想"MyTable"還是一個非預設的序列名稱。id仍然假設我個人從未使用過的列名，因為它不是描述性的。

引用自：https://dba.stackexchange.com/questions/116054