Trigger seems to be fired after transaction, not after command/statement

Question

I have two tables foo and bar, and an after trigger on foo which changes something on bar table.

create table foo(id serial primary key, _key char(128) not null);
create table bar(id bigserial primary key, _key_p char(256) not null);

So when I have a transaction, such as invoking a function like below, the trigger is fired after the transaction, not the statement or related dml action.
The trigger for foo table:

create or replace function foo_trg_func()returns trigger as $$
declare k_p char(256);begin
k_p:=(select res from prepare_pa(new.key));
insert into bar(_key_p) values(k_p);--insert it to the bar
end $$ language plpgsql;
--
create trigger foo_trg
after insert on foo
for each row execute procedure foo_trg_func();

The sample function/transaction

create or replace function `bas`(int,character(128))returns int as $$
-- some commands
with res as (select res as "d" from c_key($1,$2)),
-- attemp to insert into foo and expect the insertion to bar too
ins as (insert into foo(_key) select d from res returning 1)  --line[5]
-- check the effect of the foo_trg
select _key_p from bar,res where _key_p=res.d;    --line[7]

$$ language sql

The trigger is called and data is inserted to the bar table by foo trigger, but after the function call, and I cannot get the result of trigger insert at line 7. How may I do this now?

I also have to mention that it's possible to mark the trigger as instead or before, but it will cause many changes, so I wondered if it's possible to do with after triggers.

Answer 1

Why?

First, you must be aware that you are running a single statement (with multiple CTEs.)

Common table expressions of a single query see the same snapshot of the database. Each CTE (and the final command) can reuse output from previous CTEs (internal temporary tables), but effects on underlying tables are invisible. Your final SELECT cannot see any effects of the CTE ins on underlying tables.

A separate SELECT afterwards (even in the same transaction, can be in the same function) will see the effects.

See:

• Are SELECT type queries the only type that can be nested?

Solution for your case

You have two conflicting requirements:

1. You want to use changed rows form an underlying table (not available from the RETURNING clause of the CTE, because those are side effects from a trigger on another table). But the changes are not visible inside the same statement.

2. You want combine that with values returned from a CTE with the RETURNING clause, but those are not visible outside the statement with CTEs.

Resolve this by materializing the derived table from a CTE in a TEMPORARY TABLE:

CREATE OR REPLACE FUNCTION bas (int, text)
  RETURNS SETOF int
  LANGUAGE plpgsql AS
$func$
BEGIN
   CREATE TEMP TABLE foo_tmp (LIKE foo) ON COMMIT DROP;

   WITH ins AS (
      INSERT INTO foo(_key)
      SELECT res FROM c_key($1,$2) f
      RETURNING foo.*
      )
   INSERT INTO foo_tmp
   SELECT * FROM ins;

   RETURN QUERY
   SELECT b._key_p
   FROM   bar b
   JOIN   foo_tmp f ON b._key_p = f._key;
END
$func$;

Alternatively, you could remove the trigger and do the INSERT manually (if that's a single point of entry).

Asides

• You almost certainly don't want character(128). Use varchar(128), or just varchar or text:

• Any downsides of using data type "text" for storing strings?

• No backticks for identifiers (`res`). Looks like spillover from MySQL, which is nonsense in Postgres. Use double-quotes instead (only where you must).

• Your trigger function can be much simpler / cheaper:

CREATE OR REPLACE FUNCTION foo_trg_func()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO bar(_key_p)
   SELECT res FROM prepare_pa(NEW.key);
END
$func$;

Or simpler yet if prepare_pa() returns a single value.