sql - 我可以在 PL/pgSQL 函数内的赋值中使用 SQL 语句吗？

Question

我有这两个表(Encomenda 和 Informacaofaturacao)，我正在尝试创建一个触发器以在插入 Encomenda 之前在 Informacaofaturacao 上插入一个新行，并将 Informacaofaturacao 新行的 ID 放在 Encomenda 的新行上。

我做错了什么？

谢谢

CREATE TABLE Encomenda
(
    EncomendaID SERIAL,
    ClienteID integer NOT NULL,
    MoradaFaturacaoID integer NOT NULL,
    MoradaEnvioID integer NOT NULL,
    InformacaofaturacaoID integer NULL,
    Data timestamp NOT NULL,
    Estado EstadoEncomenda NOT NULL DEFAULT 'Em processamento',
    CONSTRAINT PK_Encomenda PRIMARY KEY (EncomendaID)
)
;

CREATE TABLE Informacaofaturacao
(
    InformacaofaturacaoID SERIAL,
    MetodopagamentoID integer NULL,
    Portes real NULL,
    Iva real NULL,
    Total real NULL,
    CONSTRAINT PK_Informacaofaturacao PRIMARY KEY (InformacaofaturacaoID)
)
;

CREATE OR REPLACE FUNCTION insert_encomenda() 
RETURNS TRIGGER 
AS $$
BEGIN
    NEW.InformacaofaturacaoID := (INSERT INTO Informacaofaturacao RETURNING InformacaofaturacaoID);

    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER insert_encomenda_trigger
BEFORE INSERT OR UPDATE ON Encomenda
FOR EACH ROW
    EXECUTE PROCEDURE insert_encomenda();

Answer 1

Postgres 不接受赋值中的数据修改 SQL 语句(INSERT、UPDATE 或 DELETE)。 The documentation states:

... the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine.

你应该使用 this form of executing a query with a single-row result相反。

此外，INSERT 命令必须有 VALUES 部分，它可以是:DEFAULT VALUES、VALUES(...) 或查询 see the syntax in the documentation .

CREATE OR REPLACE FUNCTION insert_encomenda() 
RETURNS TRIGGER 
AS $$
BEGIN
    INSERT INTO Informacaofaturacao(InformacaofaturacaoID) 
    VALUES(DEFAULT) 
    RETURNING InformacaofaturacaoID
    INTO NEW.InformacaofaturacaoID;

    RETURN NEW;
END $$ LANGUAGE plpgsql;