PLPGSQL - extra column existence in trigger
Hello!
We stored the username in a temporary table which was created by the client
app.
With this technique we can log these names in triggers too.
Now we extend it with user id and later the comp name.
Because we can update the client applications slowly, some client's tables
have these one or two extra fields, some not.
So in the new trigger we can't load them all with:
select username, userid, usercompname
into uname, uid, ucomp from tmp_userauth limit 1;
or
FOR rec IN select * from tmp_userauth limit 1 ...
IF ColumnExists(rec, 'uid') THEN --- ????
uid = rec.uid
So what is the best way to load the field values from the table?
Is there any way to know which field exists / avoid error or exception?
select username, getvaluewithouterror(userid, -1)...
So is there any syntax to not fall on missing columns?
Thank you!
Best regards,
dd
On Saturday, May 7, 2022, Durumdara <durumdara@gmail.com> wrote:
So is there any syntax to not fall on missing columns?
No. I’d probably approach this by generically converting the NEW record to
json and working with that. Non-existent object keys return null when
accessed.
David J.
Dear David!
That was a very good idea! I have integrated it! :-)
Priorly I had another idea: I made a DDL to extend the table with "add
column if not exists" elements.
But what you suggested is better. Thank you!
Best wishes
dd
David G. Johnston <david.g.johnston@gmail.com> ezt írta (időpont: 2022.
máj. 7., Szo, 16:41):
Show quoted text
On Saturday, May 7, 2022, Durumdara <durumdara@gmail.com> wrote:
So is there any syntax to not fall on missing columns?
No. I’d probably approach this by generically converting the NEW record
to json and working with that. Non-existent object keys return null when
accessed.David J.
On 2022-05-07 15:02:09 +0200, Durumdara wrote:
We stored the username in a temporary table which was created by the client
app.
With this technique we can log these names in triggers too.Now we extend it with user id and later the comp name.
Because we can update the client applications slowly, some client's tables have
these one or two extra fields, some not.So in the new trigger we can't load them all with:
How do you get a new trigger on one table but not the new columns on the
other table? Wouldn't you update both at the same time?
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
On Sat, May 07, 2022 at 07:41:44AM -0700, David G. Johnston wrote:
No. I’d probably approach this by generically converting the NEW record to
json and working with that. Non-existent object keys return null when
accessed.
One note - in my tests working with hstore was significantly faster than
json.
It could have changed since I wrote it, but you might want to check it
out:
https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/
depesz
Hello!
Peter J. Holzer <hjp-pgsql@hjp.at> ezt írta (időpont: 2022. máj. 11., Sze,
0:44):
On 2022-05-07 15:02:09 +0200, Durumdara wrote:
So in the new trigger we can't load them all with:
How do you get a new trigger on one table but not the new columns on the
other table? Wouldn't you update both at the same time?
The needed columns are in a temporary table. Each Win32 application creates
his own temp table with User Informations, like ID, Name, Computer Info.
These are for logging purposes.
The trigger is in another table, and I want to log the actual user
information with the row changing to see who caused it.
Best regards,
dd