Thread: PLPGSQL - extra column existence in trigger

PLPGSQL - extra column existence in trigger

From
Durumdara
Date:
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

Re: PLPGSQL - extra column existence in trigger

From
"David G. Johnston"
Date:
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. 

Re: PLPGSQL - extra column existence in trigger

From
Durumdara
Date:
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):
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. 

Re: PLPGSQL - extra column existence in trigger

From
"Peter J. Holzer"
Date:
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

Re: PLPGSQL - extra column existence in trigger

From
hubert depesz lubaczewski
Date:
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



Re: PLPGSQL - extra column existence in trigger

From
Durumdara
Date:
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