Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

2018-02-16 13:20 GMT+01:00 Thiemo Kellner <thiemo@xxxxxxxxxxxxxxxxxxxx>:
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].


Why you don't create query like

EXECUTE 'SELECT xxx FROM TAB WHERE A = $1.x AND B = $1.y'  USING NEW;

I don't understand tou your case, but usually count(*) > 0 looks like antipattern - probably you want to use EXISTS(...)

Regards

Pavel
 
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

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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux