Getting data from a record variable dynamically

From: "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Getting data from a record variable dynamically
Date: 2022-07-04 23:02:39
Message-ID: CACg0vTnDD1G3GgduCAxuU-41VezxPPKowTOGhkFbrJsXXuGdgw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings All,

I have a trigger that is attached to several different tables. In the
trigger function I retrieve a single row and I want the info from a
specific column. This column is dependent on the table in question.
and I have the column name stored in a variable as well. Without
writing a conditional for each table, what is the best way to
dynamically get the data from the record variable?

The only workaround I can think of is to wrap the query that populates
the record variable in a to_jsonb function and then subscript that.
Like so:

DO $$
DECLARE
rec record;
colname text;
BEGIN
SELECT to_jsonb(n.*) FROM kgn21.__nodes n limit 1 INTO rec;
colname = 'lw_id'; -- colname is different for each table
RAISE NOTICE '%', rec.to_jsonb['lw_table'];
END;
$$ language plpgsql;

Is there a better way?

Regards,

Rhys
Peace & Love | Live Long & Prosper

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-07-05 01:03:17 Re: Getting data from a record variable dynamically
Previous Message Christoph Berg 2022-07-04 09:29:04 Re: AIX and EAGAIN on open()