I have an INSTEAD OF INSERT trigger on a view. Its trigger function calls current_query(). When inserting something directly into this view, everything works fine.
But when something is inserted into this view by a “multi-statement query” or some other function/code-block, current_query() returns the whole top-level query, instead of just the single INSERT statement.
To clarify, here is a full minimum reproducible example:
Setup:
SET client_min_messages TO NOTICE;
CREATE TABLE usr (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
first_name TEXT NOT NULL,
CONSTRAINT usr_pkey PRIMARY KEY (id)
);
CREATE VIEW usr_view AS
SELECT id, first_name
FROM usr;
CREATE FUNCTION on_insert_view() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
current_query text;
BEGIN
SELECT CURRENT_QUERY()
INTO current_query;
RAISE NOTICE 'current_query: %', current_query;
RETURN NEW;
END;
$$;
CREATE TRIGGER on_insert_view INSTEAD OF INSERT ON usr_view FOR EACH ROW EXECUTE FUNCTION on_insert_view();
Query 1 (single statement):
INSERT INTO usr_view (first_name) VALUES ('John');
Returns:
NOTICE: current_query: INSERT INTO usr_view (first_name) VALUES ('John');
Query 2 (multi-statement query):
INSERT INTO usr_view (first_name) VALUES ('John');
INSERT INTO usr_view (first_name) VALUES ('Bob');
Returns:
NOTICE: current_query: INSERT INTO usr_view (first_name) VALUES ('John');
INSERT INTO usr_view (first_name) VALUES ('Bob');
NOTICE: current_query: INSERT INTO usr_view (first_name) VALUES ('John');
INSERT INTO usr_view (first_name) VALUES ('Bob');
INSERT 0 1
Query 3 (anonymous code block):
DO $$
BEGIN
INSERT INTO usr_view (first_name) VALUES ('John');
END;
$$;
Returns:
NOTICE: current_query: DO $$
BEGIN
INSERT INTO usr_view (first_name) VALUES ('John');
END;
$$;
My expectation was that current_query() would return the same thing as it returns for Query 1 for all the three queries listed above.
Unfortunately it does not… So my question is, if there is a way to get the statement that actually triggers the trigger function, as opposed to getting the top-level query that this statement is a part of?
7
a way to get the statement that actually triggers the trigger function, as opposed to getting the top-level query that this statement is a part of?
You’ll need to consult special trigger variables TG_OP and TG_TABLE_NAME and correlate that with the ouput of current_query().
I think your understanding of multi-statement query might be off. The doc says:
current_query () → text
Returns the text of the currently executing query, as submitted by the client (which might contain more than one statement).
From what you showed, not one of the examples contains more than one statement. The do block could. Using CTEs could be another example:
with cte1 as (insert into usr_view(first_name)
values ('John')
returning id)
, cte2 as (delete from usr_view
where id not in (select id
from cte1) )
insert into usr_view(first_name)
values ('Bob');
The insert is one statement, the delete is another, then the last, outermost insert is yet another, third statement, and they all come in a single query. What you did is divide them into separate, single-statement queries:
INSERT INTO usr_view (first_name) VALUES ('John');
INSERT INTO usr_view (first_name) VALUES ('Bob');
And I believe your client, like mine, responded with a notice for the first one first, then another notice for the second one, separately. After all, the function offers to report the entire currently processed query, even if there are multiple statements in it. It does not promise to report multiple queries that the client sent together.
Multiple statements can make up a single query but multiple queries are just multiple queries – sometimes called a pipeline.
Your example of the do anonymous procedural code block is also single-statement but if you did add some more, current_query() would show the whole thing:
DO $$
BEGIN
INSERT INTO usr_view (first_name) VALUES ('John');
UPDATE usr_view SET first_name=first_name||' UPDATED';
END;
$$;
NOTICE: current_query: DO $$ BEGIN INSERT INTO usr_view (first_name) VALUES ('John'); UPDATE usr_view SET first_name=first_name||' UPDATED'; UPDATE another_thing SET a=1; END; $$;
DO
And if the trigger fired on the first one, TG_TABLE_NAME would be usr_view and TG_OP would show INSERT, pointing you to the statement responsible.
3