Hi 2018-02-16 13:20 GMT+01:00 Thiemo Kellner <thi...@gelassene-pferde.biz>:
> 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. > >
