postgresql - PL/pgSQL: General Way to Update N Columns in Trigger?

Question

I am attempting create a function that will take a general table and convert N columns to upper case. I haven't had any luck finding a solution to this type of 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'm 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 on another table:

insert into table_two ('a', 3);
>> A, 3

etc.

Answer 1

This is a very tricky question.

Your attempt is bound to fail, because the current row variable NEW is not visible inside EXECUTE. And even if it was, NEW is a row type (a record), not an array. Unlike array elements, fields of a row cannot be referenced by numerical index. This would cause all kinds of problems in SQL, because (as opposed to an array) each field can have a different data type, and SQL expects to know the data type to deal with beforehand. Very tricky indeed.

Generic method targeted at selected types only

Fortunately, we have dealt with a similar problem before:

• How to set value of composite variable field using dynamic SQL

You'll find ample explanation there.
Adapted for a trigger function and depending on the data type of columns, it could 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 as well, 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();

SQL Fiddle.

Simple, unspecific method

As long as you are using simple types in your tables only and want to upper-case all character data, there is another crude, fast and simple method: Cast the whole row to text, upper-case the text representation, cast back to the row type and update NEW with the result. Details about the row type of a table

• Check a whole table for a single value

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;

SQL Fiddle.

We have to decompose the row type, because SELECT INTO assigns individual fields of a target row type one-by-one. We cannot assign the whole row at once. If you look closely, the "generic" solution does the same, less obvious.

While character data is case-sensitive in text representation, other basic numeric or date / time data types are not. So the simple method works reliably. Probably with most other types, too. But I did not test with others, and there certainly are exception. You'll have to verify for the data types you use.

Also, while the code is much shorter and simpler than with the generic method, this is not necessarily faster, especially with lots of unaffected columns. It's probably a lot faster in simple cases, though.