Dynamically accessing columns from a row type in a trigger

On 8/12/23 13:09, Rhys A.D. Stewart wrote:

Greetings all,

I am writing a trigger and would like to know how to dynamically
access a column from the "OLD" variable. pk_col is the column name
from the table.

I've come up with either doing this:
EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;

which looks a bit excessive, or this

pk_val = to_jsonb(OLD.*)->pk_col

which looks cleaner, but then I am having to incur a little overhead
by using the to_jsonb function. Ideally, something like this would be
great:

pk_val = OLD[pk_col]

Well if want/can use plpython3u you can do just that:

https://www.postgresql.org/docs/current/plpython-trigger.html

as:

pk_val = TD["old"][pk_col]

but evidently we can't subscript ROW types.

Am I missing out on a simpler or more elegant solution?

Rhys
Peace & Love | Live Long & Prosper

--
Adrian Klaver
adrian.klaver@aklaver.com

On 8/12/23 13:09, Rhys A.D. Stewart wrote:

Greetings all,

I am writing a trigger and would like to know how to dynamically
access a column from the "OLD" variable. pk_col is the column name
from the table.

I've come up with either doing this:
EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;

Got focused on pk_val = OLD[pk_col] and missed the low hanging fruit:

pk_val = OLD.pk_col

which looks a bit excessive, or this

pk_val = to_jsonb(OLD.*)->pk_col

which looks cleaner, but then I am having to incur a little overhead
by using the to_jsonb function. Ideally, something like this would be
great:

pk_val = OLD[pk_col]

but evidently we can't subscript ROW types.

Am I missing out on a simpler or more elegant solution?

Rhys
Peace & Love | Live Long & Prosper

--
Adrian Klaver
adrian.klaver@aklaver.com

Hey Adrian,

Thanks for your response. I don't think I explained myself clearly.
pk_col is not the column name. pk_col is a variable that holds the
name of a column. This is one trigger for several tables with varying
structures. So pk_col would be a column specific to the current
TG_TABLE_NAME when the trigger is invoked. This is why in my example I
had to use EXECUTE to get the value of the pk_col from OLD.

Actually, now that I'm thinking about it, I don't really want to store
the value into a variable because the pk_col might be of any given
type. So ideally, I'd love a way to just get the value from OLD and
use it directly in another query. Something along the lines of:

`EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
USING OLD['pk_col']`.

I reckon I may have to look at just generating a trigger function per
table, or maybe look into using TG_ARGS.

Rhys
Peace & Love | Live Long & Prosper

On 8/12/23 20:21, Rhys A.D. Stewart wrote:

Hey Adrian,

Thanks for your response. I don't think I explained myself clearly.
pk_col is not the column name. pk_col is a variable that holds the
name of a column. This is one trigger for several tables with varying
structures. So pk_col would be a column specific to the current
TG_TABLE_NAME when the trigger is invoked. This is why in my example I
had to use EXECUTE to get the value of the pk_col from OLD.

So you are looking for a generic solution.

Actually, now that I'm thinking about it, I don't really want to store
the value into a variable because the pk_col might be of any given
type. So ideally, I'd love a way to just get the value from OLD and
use it directly in another query. Something along the lines of:

The issue then is determining what value to get from OLD.

Is that generic e.g always the Primary Key or will it vary?

`EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
USING OLD['pk_col']`.

I reckon I may have to look at just generating a trigger function per
table, or maybe look into using TG_ARGS.

Rhys
Peace & Love | Live Long & Prosper

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, Aug 12, 2023 at 1:10 PM Rhys A.D. Stewart <rhys.stewart@gmail.com>
wrote:

Am I missing out on a simpler or more elegant solution?

No, you are not (at least among SQL and pl/pgsql. SQL is strongly and
statically typed. Circumventing that has a cost, but at least you do have
tools at hand when you find the need.

David J.

On Sun, Aug 13, 2023 at 11:27 AM Rhys A.D. Stewart
<rhys.stewart@gmail.com> wrote:

Hey Adrian,

Thanks for your response. I don't think I explained myself clearly.
pk_col is not the column name. pk_col is a variable that holds the
name of a column. This is one trigger for several tables with varying
structures. So pk_col would be a column specific to the current
TG_TABLE_NAME when the trigger is invoked. This is why in my example I
had to use EXECUTE to get the value of the pk_col from OLD.

Actually, now that I'm thinking about it, I don't really want to store
the value into a variable because the pk_col might be of any given
type. So ideally, I'd love a way to just get the value from OLD and
use it directly in another query. Something along the lines of:

`EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
USING OLD['pk_col']`.

I reckon I may have to look at just generating a trigger function per
table, or maybe look into using TG_ARGS.

google lead me to this post:
https://stackoverflow.com/questions/55245353/access-dynamic-column-name-of-row-type-in-trigger-function

table, or maybe look into using TG_ARGS.

maybe you are referring to TG_ARGV.

example of TG_ARGV =>
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/triggers.out

Hello again

Actually, now that I'm thinking about it, I don't really want to store
the value into a variable because the pk_col might be of any given
type. So ideally, I'd love a way to just get the value from OLD and
use it directly in another query. Something along the lines of:

`EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
USING OLD['pk_col']`.

I reckon I may have to look at just generating a trigger function per
table, or maybe look into using TG_ARGS.

So the less obvious solution that works is to create a temporary
table. A little verbose, but I get to keep the types.

`CREATE TEMPORARY TABLE _ ON COMMIT DROP AS SELECT OLD.*;`

_ as a table name makes things a little easier to type.

Rhys
Peace & Love | Live Long & Prosper

So the less obvious solution that works is to create a temporary

table. A little verbose, but I get to keep the types.

`CREATE TEMPORARY TABLE _ ON COMMIT DROP AS SELECT OLD.*;`

_ as a table name makes things a little easier to type.

Rhys
Peace & Love | Live Long & Prosper

If the connection creating the table is long-lived, maybe make the table
once and truncate instead.

Well, some weeks ago, I read an article about that.

The more efficient approach (in pgplsql) is to use hstore.

With a similar topic, at the end, I created a group of functions that store
in an internal table, data structure for each table under the same generic
trigger.
I also store, primary key.
The goal of this approach is to compare easley old and new values,
discarding automatic and special (for my project) fields, so I can manage
historical versions, audit information etc...

Il giorno sab 12 ago 2023 alle ore 22:16 Rhys A.D. Stewart <
rhys.stewart@gmail.com> ha scritto:

Greetings all,

I am writing a trigger and would like to know how to dynamically
access a column from the "OLD" variable. pk_col is the column name
from the table.

I've come up with either doing this:
EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;

which looks a bit excessive, or this

pk_val = to_jsonb(OLD.*)->pk_col

which looks cleaner, but then I am having to incur a little overhead
by using the to_jsonb function. Ideally, something like this would be
great:

pk_val = OLD[pk_col]

but evidently we can't subscript ROW types.

Am I missing out on a simpler or more elegant solution?

Rhys
Peace & Love | Live Long & Prosper

--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]