In postgresql trigger function ------- How can I use NEW variable as an array?

Question

We can use NEW.columnname and we get it's value.

Can I use NEW variable as array to get it's values? like NEW[1] or NEW.1 ...Or if I pass column names in one text array to trigger function argument then can I use NEW.$1 to get 1st column value?

I have to do it only with language plpgsql

NEW.column_name should work; Using the ordinal position is brittle -- it can cause problems if someone changes the column order. — OMG Ponies
– OMG Ponies, Commented Jun 19, 2011 at 17:48
I am trying NEW.variable this variable is of type text and contains column_name but NEW.variable recognizes "variable" as key not its value.... — mitesh
– mitesh, Commented Jun 19, 2011 at 20:02
possible duplicate of PostgreSQL trigger to generate codes for multiple tables dynamically — Denis de Bernardy
– Denis de Bernardy, Commented Jun 20, 2011 at 8:00
Why doesn't your code know what columns it's dealing with? Why do you wish to impose an (assumed) ordering on the columns within a table? You've described a solution you're attempting to implement (which isn't going to work well, if at all) without telling us the underlying problem you're trying to solve - which we may be far better placed to provide suggestions for. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 20, 2011 at 8:26

Dutow · Accepted Answer · 2011-06-19 18:22:52Z

1

You can convert a row / rows to and from array(s), as described here.

answered Jun 19, 2011 at 18:22

Dutow

5,6581 gold badge33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mitesh Over a year ago

I saw your code but it is not helpful for me because I want to make operation on NEW variable as NEW.in_array[0] this thing is not working

Nigel Ren Over a year ago

This is an example of when links become out of date making the answer of little help.

Community · Accepted Answer · 2017-05-23 12:20:01Z

0

I've suggested a dup question, since it's really the same underlying issue.

I'd be quite enthusiastic to be shown wrong (I occasionally need this myself too), but best I'm aware, referring column names using variables is one of those cases where you actually need to use PL/C triggers rather than PL/PgSQL triggers. You'll find examples of such triggers in contrib/spi and on PGXN.

Alternatively, write a function that writes your trigger using execute statements.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jun 20, 2011 at 8:02

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

In postgresql trigger function ------- How can I use NEW variable as an array?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related