Sanitize input to a column in postgres

Question

So, I think this should be fairly simple, but the documentation makes it seem somewhat more complicated. I've written an SQL function in PostgreSQL (8.1, for now) which does some cleanup on some string input. For what it's worth, the string is an LDAP distinguished name, and I want there to consistently be no spaces after the commas - and the function is clean_dn(), which returns the cleaned DN. I want to do the same thing to force all input to another couple of columns to lower case, etc - which should be easy once I figure this part out.

Anyway, I want this function to be run on the "dn" column of a table any time anyone attempts to insert to or update and modify that column. But all the rule examples I can find seem to make the assumption that all insert/update queries modify all the columns in a table all the time. In my situation, that is not the case. What I think I really want is a constraint which just changes the value rather than returning true or false, but that doesn't seem to make sense with the SQL idea of a constraint. Do I have my rule do an UPDATE into the NEW table? Do I have to create a new rule for every possible combination of NEW values? And if I add a column, do I have to go through and update all of my rule combinations to refelect every possible new combination of columns?

There has to be an easy way...

And yes, I know I can do the validation on the client side. It's dumb to repeat code for every place I touch this value if the database can just have the rule defined once.. :) — dannysauer, Oct 08 '12 at 22:47
You might be able to do something as simple as `ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (clean_dn(dn) = dn)` — Frank Farmer, Oct 09 '12 at 23:21

Erwin Brandstetter · Answer 1 · 2012-10-09T23:14:41.583

First, update to a current version of PostgreSQL. 8.1 is long dead and forgotten und unsupported and very, very old .. you get my point? Current version is PostgreSQL 9.2.

Then, use a trigger instead of a rule. It's simpler. It's the way most people go. I do.

For column col in table tbl ...

First, create a trigger function:

CREATE OR REPLACE FUNCTION trg_tbl_insupbef()
  RETURNS trigger AS
$BODY$
BEGIN

NEW.col := f_myfunc(NEW.col);  -- your function here, must return matching type

RETURN NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Then use it in a trigger.
For ancient Postgres 8.1:

CREATE TRIGGER insupbef
  BEFORE INSERT OR UPDATE
  ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_insupbef();

For modern day Postgres (9.0+)

CREATE TRIGGER insbef
  BEFORE INSERT OR UPDATE OF col  -- only call trigger, if column was updated
  ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_insupbef();

You could pack more stuff into one trigger, but then you can't condition the UPDATE trigger on just the one column ...

I'd love to upgrade the dbms, but there's a whole support mess on this server which means it had to run RHEL 5. I'm saddled with an old postgres, an old python, an old perl, etc. If it was mine to manage... anyhow, triggers do look like the way to go. And maybe some more pressure on the owner to move to a supported version... — dannysauer, Oct 09 '12 at 03:24

Sanitize input to a column in postgres

1 Answers1

Linked