Dynamic PL/pgSQL select query: value association propblem

From: Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Dynamic PL/pgSQL select query: value association propblem
Date: 2018-02-16 12:20:49
Message-ID: 20180216132049.76333zk3lgrasquc@www.gelassene-pferde.biz
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

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mariusz 2018-02-16 12:31:06 strange construct with RETURN within plpgsql
Previous Message Michael Krüger 2018-02-16 10:42:32 Re: Parallel Query - Can it be used within functions?