PostgreSQL force upper case for all data
Forcing correct values is one thing. Simple CHECK
constraints per column can do the job reliably:
CREATE TABLE foo
foo_id serial PRIMARY KEY
, text_column text CHECK (upper(text_column) = text_column)
, ...
);
Auto-correcting all input is another thing, and not as simple. But it can be done with a generic trigger function that looks up column names and data types in the system catalog and converts all character data to upper case. Safe enough, but not quite as fast and not as bullet-proof because more complex and triggers can more easily be circumvented or counteracted by other triggers.
One generic trigger function for all:
CREATE OR REPLACE FUNCTION trg_all_upper()
RETURNS trigger AS
$BODY$
DECLARE
-- basic character types. possibly add citext, domains or custom types
_typ CONSTANT regtype[] := '{text, bpchar, varchar, \"char\"}';
_sql text;
_found bool;
BEGIN
SELECT INTO _sql, _found
'SELECT ' || string_agg(
CASE WHEN a.atttypid = ANY(_typ)
THEN format ('upper(%1$s)::%2$s AS %1$s'
, a.col, a.atttypid::regtype)
ELSE col END
, ', ') || ' FROM (SELECT ($1).*) t'
, bool_or(a.atttypid = ANY(_typ))
FROM (
SELECT a.atttypid, quote_ident(attname) AS col
FROM pg_attribute a
WHERE a.attrelid = TG_RELID -- object ID of table that fired trigger
AND a.attnum >= 1 -- exclude tableoid & friends
AND NOT a.attisdropped -- exclude dropped columns
ORDER BY a.attnum
) a;
-- RAISE NOTICE '%', _sql;
IF _found THEN
EXECUTE _sql USING NEW INTO NEW;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql VOLATILE;
A trigger per table:
CREATE TRIGGER all_upper_bef_insupd
BEFORE INSERT OR UPDATE ON big
FOR EACH ROW EXECUTE PROCEDURE trg_all_upper();
Converts all values in columns with a character data type as defined in _typ
to upper case.
Tested in Postgres 9.4.
Related:
- Create Alias for PostgreSQL Table
- Replace empty strings with null values
- How to set value of composite variable field using dynamic SQL