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:
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
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.
