Re: Dynamic PL/pgSQL select query: value association propblem - Mailing list pgsql-general

    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;

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.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite