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

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