Re: Dynamic PL/pgSQL select query: value association propblem

Zitat von Daniel Verite <daniel(at)manitou-mail(dot)org>:

> Thiemo Kellner, NHC Barhufpflege wrote:
>
>> > Why you don't create query like
>> >
>> > EXECUTE 'SELECT xxx FROM TAB WHERE A = $1.x AND B = $1.y' USING NEW;
>>
>> I shall try. This would be the direct way, but I doubt the placeholder
>> $1 can be a record.
>
> It could be written without refering to any individual column:
>
> IF EXISTS (select 1 from tablename
> where tablename.* is not distinct from NEW)
> THEN
> -- do something
> END IF;

Wow, I shall see if I can apply this pattern to more.

> But since the select cannot see not-yet-committed changes from other
> sessions, such a trigger cannot reliably detect duplicates, unless
> you make sure that there are no concurrent writes to the table.

This is not trigger specific but always a problem of concurrency. If
you let two processes do SCD2 on the same table at the same time on
the same business keys, you will get a messed-up table.

--
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.