Thread: PLPGSQL - extra column existence in trigger
Hello!
So is there any syntax to not fall on missing columns?
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;
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.uidSo 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)...
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
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):
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!"
Attachment
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