Thread: Getting data from a record variable dynamically

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

> On 5 Jul 2022, at 1:02, Rhys A.D. Stewart <rhys.stewart@gmail.com> wrote:
>
> 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?

I would create a simple trigger function for each of those tables that just extracts the value(s) from the field(s) you
mentioned,and then pass those values on to a generic function that does the actual table-independent work. 

The usual trigger pseudo-columns and variables wouldn’t be available in that generic function, but considering that
yourtriggers fire from different tables, you could either pass them on or they (probably) don’t make sense in the
contextof the generic function. 

Alban Hertroys
--
There is always an exception to always.