From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Dynamic PL/pgSQL select query: value association propblem |
Date: | 2018-02-16 12:56:16 |
Message-ID: | CAFj8pRBJfb+Hj9MjbnLWghbhPPOxKe8GCmz3spuFSu-nQa65yQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
2018-02-16 13:20 GMT+01:00 Thiemo Kellner <thiemo(at)gelassene-pferde(dot)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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | mariusz | 2018-02-16 13:08:04 | Re: strange construct with RETURN within plpgsql |
Previous Message | Durumdara | 2018-02-16 12:52:20 | Re: Remove default privilege from DB |