Re: Batch API for After Triggers

On 9 June 2013 12:58, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 06/09/2013 04:58 PM, Simon Riggs wrote:
>> There are also difficulties in semantics, since when
>> we have OLD and NEW at row level we know we are discussing the same
>> row. With sets of OLD and NEW we'd need to be able to link the
>> relations back together somehow, which couldn't be done by PK since
>> that could change.
>
> We don't currently have OLD and NEW relations so we're free to define
> how this works pretty freely.
>
> Rather than having OLD and NEW as separate relations, we could just have
> one OLD_AND_NEW relation. In that relation we exploit Pg's composite
> types to nest the old and new tuples in a single outer change record.
>
> OLD_AND_NEW would look to PL/PgSQL as if it were:
>
> CREATE TEMPORARY TABLE OLD_AND_NEW (
> OLD tabletype NOT NULL,
> NEW tabletype NOT NULL
> );
>
> ...though presumably without the ability to create indexes on it and the
> other things you can do to a real temp table. Though I can see cases
> where that'd be awfully handy too.
>
> For DELETE and INSERT we'd either provide different relations named OLD
> and NEW respectively, or we'd use OLD_AND_NEW with one field or the
> other blank. I'm not sure which would be best.
>
> Alternately, we could break the usual rules for relations and define OLD
> and NEW as ordered, so lock-step iteration would always return matching
> pairs of rows. That's useless in SQL since there's no way to achieve
> lock-step iteration, but if we provide a
> "for_each_changed_row('some_function'::regproc)" that scans them in
> lock-step and invokes `some_function` for each one...? (I haven't yet
> done enough in the core to have any idea if this approach is completely
> and absurdly impossible, or just ugly. Figured I'd throw it out there
> anyway.)

I think the best way, if we did do this, would be to have a number of
different relations defined:

OLD
NEW
INSERTED
DELETED
all of which would be defined same as main table

and also one called
UPDATED
which would have two row vars called OLD and NEW
so you would access it like e.g. IF UPDATED.OLD.id = 7

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services