CODEWITHSUNDEEP
sqlpostgresqltriggersplpgsql
Peter
Peter

Reputation: 467

Return a table when a trigger function is called

I need some_fun() to be executed before a trigger. It will return a table.

I ran this:

INSERT INTO SomeTable(some_bool) VALUES (true);

I expected this:

 returnColHeader
------------------
12
23
23
(3 row)

But I got this:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

I've read documentation on TRIGGERS https://www.postgresql.org/docs/current/plpgsql-trigger.html and also RETURNING CLAUSE https://www.postgresql.org/docs/9.5/dml-returning.html and a few other postgres related readings but I'm still unable to solve my problem.

DROP TRIGGER IF EXISTS run_some_fun on SomeTable CASCADE;

CREATE TRIGGER run_some_fun
BEFORE INSERT ON SomeTable
FOR EACH ROW WHEN (NEW.some_bool = TRUE)
EXECUTE FUNCTION run_some_fun();

CREATE OR REPLACE FUNCTION run_some_fun()
RETURNS TRIGGER AS $$
BEGIN
    SELECT some_fun(NEW.eid); -- This is wrong and throws error
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION some_fun(eID INT)
RETURNS TABLE (returnColHeader INT) AS $$
BEGIN
    RETURN QUERY
    SELECT eid FROM Joins j1;
END;
$$ LANGUAGE plpgsql;

Upvotes: 0

Views: 965

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45805

You cannot to return anything from after trigger. The trigger functions can returns value of composite type, but the returned value from after trigger is ignored. There is not any chance for what you want. And it looks little bit scary.

Upvotes: 1

Related Questions