PL / pgSQL: general way to update N columns in triggers?
I am trying to create a function that will take a general table and convert N columns to uppercase. I had no luck finding a solution to this problem, but I was able to come up with the following:
create or replace function uc_on_insert()
returns trigger as
$$
declare
p_tbl varchar = TG_TABLE_NAME;
p_sch varchar = TG_TABLE_SCHEMA;
i varchar;
begin
for i in
(select column_name
from INFORMATION_SCHEMA.COLUMNS
where 1=1
and table_name ilike p_tbl
and table_schema ilike p_sch
and data_type ='character varying')
loop
execute 'new.' || i || ' = upper(new.' || i || ');';
return new;
end loop;
end;
$$ language plpgsql;
I am currently getting this error:
ERROR: syntax error at or near "new"
LINE 1: new.c1 = upper(new.c1);
^
QUERY: new.c1 = upper(new.c1);
The expected input would be, on any table I have this trigger:
insert into table_one('a', 'b');
>> A, B
and if i put this trigger in another table:
insert into table_two ('a', 3);
>> A, 3
etc..
source to share
That's a very difficult question.
Your attempt is in error because the current string variable is NEW
not visible internally EXECUTE
. And even if it were, it NEW
is a string type (record), not an array. Unlike array elements, string fields cannot be referenced by a numeric index. This will cause all sorts of problems in SQL because (unlike an array) each field can have a different datatype, and SQL expects to know the datatype to process in advance. Very difficult.
Generic method for selected types only
Fortunately, we ran into a similar problem earlier:
You will find a sufficient explanation here.
Adapted for trigger function and depending on the data type of the columns, it might look like this:
Trigger function
CREATE OR REPLACE FUNCTION trg_uc_on_insert()
RETURNS trigger AS
$func$
BEGIN
EXECUTE 'SELECT ' || array_to_string(ARRAY(
SELECT CASE WHEN atttypid = 'varchar'::regtype
-- atttypid = ANY('{text, bpchar, varchar}'::regtype[])
THEN 'upper(($1).' || quote_ident(attname)
|| ')::' || atttypid::regtype::text
ELSE '($1).' || quote_ident(attname)
END AS fld
FROM pg_catalog.pg_attribute
WHERE attrelid = pg_typeof(NEW)::text::regclass
AND attnum > 0
AND attisdropped = FALSE
ORDER BY attnum
), ',')
USING NEW
INTO NEW;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
If you want to apply the same rule to other basic character types, use the commented alternative.
Trigger
CREATE TRIGGER trg_t_insbef
BEFORE INSERT
ON t -- works for any table
FOR EACH ROW
EXECUTE PROCEDURE trg_uc_on_insert();
Simple, non-specific method
As long as you only use simple types in your tables and want to have all character data in uppercase , there is another rough, quick and simple method: cast the whole string to text
, uppercase text representation, revert to the string type, and update NEW
with the result. Details of the table row type
Trigger function
CREATE OR REPLACE FUNCTION trg_uc_simple_on_insert()
RETURNS trigger AS
$func$
BEGIN
EXECUTE 'SELECT ($1::' || pg_typeof(NEW) || ').*'
USING upper(NEW::text)
INTO NEW;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
We have to decompose the line type because it SELECT INTO
assigns separate target line type fields one by one. We cannot assign the entire line at once. If you look closely, the "generic" solution does the same, less obvious one.
While character data is case sensitive in textual representation, other underlying numeric or date data types are not. Thus, the simple method works reliably. Probably with most other types as well. But I haven't tested others and there is definitely an exception. You need to check the data types used.
Also, while the code is much shorter and simpler than the generic method, it is not necessarily faster, especially with a lot of unaffected columns. This is probably much faster in simple cases.
source to share