CODEWITHSUNDEEP
postgresqltriggersuser-defined-functionsplpgsql
alexanoid
alexanoid

Reputation: 25790

Dynamically Access Columns and Cast Data Types in PostgreSQL Functions

In custom functions in PostgreSQL, I can access the NEW object to retrieve the values of specific columns, for example:

NEW.description 
NEW.city

Essentially, this is a static reference at the code-writing stage. But is it possible to achieve the same dynamically, for example, similar to how it’s done in Map structures:

NEW['description'] 
NEW['name']

In other words, at the code-writing stage, I don’t know which specific column in NEW I will be referencing, as this is determined dynamically during the function’s execution.

And the same question applies to type casting—can I dynamically cast the retrieved value to a type that is also determined during the function’s execution? For example, INTEGER, BOOLEAN, etc. That is, I dynamically obtain these types as TEXT during the function's execution, perhaps from a query to another configuration table, and I would like to cast the column value from NEW to such a type.

Upvotes: 1

Views: 42

Answers (1)

Zegarek
Zegarek

Reputation: 26113

NEW is just a record that you can automagically convert to a jsonb with to_jsonb(NEW) and get the exact syntax and behaviour you want:
demo at db<>fiddle

create table test(
   a int generated by default as identity primary key
  ,b boolean,c text,d "char",e "char",f "char");

create table debug_(
   payload jsonb
  ,comment text
  ,is_valid_as_date boolean 
  ,id int generated by default as identity primary key
  ,ts timestamp default clock_timestamp());
select setseed(.46);

create function f_test_trigger()returns trigger as $f$
declare new_jsonb jsonb:=to_jsonb(new);
begin
  insert into debug_ values
   (new_jsonb['c'], 'just one of the columns')
  ,(new_jsonb['x'], 'this column does not exist')
  ,(new_jsonb[chr(97+(random()*25)::int)], '1st random lowercase letter key')
  ,(new_jsonb[chr(97+(random()*25)::int)], '2nd random lowercase letter key');
  update debug_ 
    set is_valid_as_date = pg_input_is_valid( payload#>>'{}'--as unquoted text
                                             ,'date');
  return new;
end $f$ language plpgsql;

create trigger test_trigger after insert on test
for each row execute function f_test_trigger();

insert into test(b,c,d,e,f)values(true,'2024-10-01','d','e','f');

table debug_;
payload comment is_valid_as_date id ts
"2024-10-01" just one of the columns TRUE 1 2025-01-15 18:26:13.079506
null this column does not exist null 2 2025-01-15 18:26:13.079812
null 1st random lowercase letter key null 3 2025-01-15 18:26:13.079839
"d" 2nd random lowercase letter key false 4 2025-01-15 18:26:13.079847

As long as the text value you're dealing with is acceptable as an input for your given target type, your cast() will work. Recycling an answer from a related thread: in PostgreSQL 16 and above, there's pg_input_is_valid() that lets you make sure before you attempt to cast().

pg_input_is_valid ( string text, type text ) → boolean
Tests whether the given string is valid input for the specified data type, returning true or false. This function will only work as desired if the data type's input function has been updated to report invalid input as a “soft” error. Otherwise, invalid input will abort the transaction, just as if the string had been cast to the type directly.

pg_input_is_valid('42', 'integer') → t
pg_input_is_valid('42000000000', 'integer') → f
pg_input_is_valid('1234.567', 'numeric(7,4)') → f

In PostgreSQL 15 and earlier, you can build your own:

create or replace function is_interpretable_as(arg text, arg_type text) 
returns boolean language plpgsql as $$
begin
    execute format('select cast(%L as %s)', arg, arg_type);
    return true;
exception when others then
    return false;
end $$;

Upvotes: 2

Related Questions