清理输入到Postgres中的列 [英] Sanitize input to a column in postgres

问题描述

所以，我认为这应该很简单，但是文档使它看起来有些复杂.我已经在PostgreSQL(目前为8.1)中编写了一个SQL函数，该函数对某些字符串输入进行了一些清理.对于它的价值，该字符串是LDAP专有名称，我希望逗号后始终没有空格-函数是clean_dn()，该函数返回已清理的DN.我想做同样的事情，以强制所有输入到另外两列的输入都变为小写字母，以此类推-一旦我弄清楚了这部分，这应该很容易.

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.

无论如何，无论何时有人尝试插入或更新和修改该列，我都希望在表的"dn"列上运行此功能.但是我能找到的所有规则示例似乎都是在假设所有插入/更新查询始终修改表中的所有列.在我的情况下，情况并非如此.我认为我真正想要的是一个约束，该约束仅更改值而不是返回true或false，但这对于约束的SQL思想似乎没有任何意义.我是否有规则对NEW表进行UPDATE?我是否必须为新值的每种可能组合创建新规则?而且，如果我添加一列，是否需要遍历并更新我的所有规则组合以体现所有可能的新列组合?

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

推荐答案

首先，更新到PostgreSQL的当前版本. 8.1已经死了很久，被遗忘了，没有人支持，而且非常非常老..您明白我的意思了吗? 当前版本为PostgreSQL 9.2 .

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.

对于表tbl中的列col ...

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;

然后在 触发 .
对于古代Postgres 8.1:

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();

对于现代Postgres(9.0 +)

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();

您可以将更多的内容打包到一个触发器中，但是您就不能仅在一个列上设置UPDATE触发器了...

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

这篇关于清理输入到Postgres中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！