Can I use a SQL statement when assigning inside a PL / pgSQL function?
I have these two tables (Encomenda and Informacaofaturacao) and I am trying to create a trigger to insert a new row into Informacaofaturacao before inserting into Encomenda and put the Informacaofaturacao new row id on the new Encomenda row.
What am I doing wrong?
thank
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();
+3
source to share
1 answer
Postgres does not accept data that modifies an SQL statement (INSERT, UPDATE, or DELETE) when assigned. The documentation states:
... an expression in such an expression is evaluated using an SQL SELECT command sent to the main database engine.
You must use this form to execute a query with a single line result .
In addition, the INSERT command must have a VALUES part, it can be: DEFAULT VALUES, VALUES (...) or query 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;
+3
source to share