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

From Igor Neyman
Subject RE: Dynamic PL/pgSQL select query: value association propblem
Date
Msg-id CO2PR17MB00585A9A68475D4CB484D8D0DACB0@CO2PR17MB0058.namprd17.prod.outlook.com
Whole thread Raw
In response to Dynamic PL/pgSQL select query: value association propblem  (Thiemo Kellner <thiemo@gelassene-pferde.biz>)
List pgsql-general
Hi all

I would like to have a generic trigger function that compares on insert if there is already a record in the table with
thevery same values. Using PL/pgSQL ( I am not bound to that) I know the insert record structure from the new record
andI can build a select query dynamically from the catalogue, but I do not know to associate the new record values to
thecorresponding columns. An example Table T has columns Q and L, in that order. If I create an insert trigger function
Ihave the new values in new.L and new.Q. From the catalogue I can create a the select query S_QUERY:
 
select count(*) > 0 from T where A = $1 and B = $2. But when I want to EXECUTE S_QUERY USING it fails because I cannot
usesomething like NEW[1].
 

Is there a way to convert the record type into an array type? Or is there even a way to do it more directly like WHERE
T.RECORD= NEW?
 

Kind regards Thiemo

--
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------

Why not let the database do this job for you?
Just create a proper UNIQUE constraint on the columns you are interested in, and catch a "duplicate key" exception in
yourapp.
 

Regards,
Igor Neyman

pgsql-general by date:

Previous
From: Thiemo Kellner
Date:
Subject: Re: Dynamic PL/pgSQL select query: value association propblem
Next
From: Durumdara
Date:
Subject: Re: Remove default privilege from DB