From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | RE: Dynamic PL/pgSQL select query: value association propblem |
Date: | 2018-02-16 14:34:58 |
Message-ID: | CO2PR17MB00585A9A68475D4CB484D8D0DACB0@CO2PR17MB0058.namprd17.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 the very same values. Using PL/pgSQL ( I am not bound to that) I know the insert record structure from the new record and I can build a select query dynamically from the catalogue, but I do not know to associate the new record values to the corresponding columns. An example Table T has columns Q and L, in that order. If I create an insert trigger function I have 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 use something 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 your app.
Regards,
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | Durumdara | 2018-02-16 14:40:42 | Re: Remove default privilege from DB |
Previous Message | Thiemo Kellner | 2018-02-16 14:23:59 | Re: Dynamic PL/pgSQL select query: value association propblem |