2

Is there a way to force all text data to uppercase without recurring to writing a function for every table or doing it client side?

  • I don't think so. Mybe trigger, but you always need to write functions. – user_0 Jun 1 '15 at 15:25
  • CHECK (upper(your_column) = your_column) - but you have to define this for every text column. – dezso Jun 1 '15 at 15:37
11

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.