创建Postgres规则或触发器以在插入时自动将列转换为小写或大写 [英] Create a Postgres rule or trigger to automatically convert a column to lowercase or uppercase on insert

问题描述

如何在postgres中创建规则以在插入的X列上强制小写。 EG插入时：

How would you create a rule in postgres to force lowercase on X column on insert. EG When inserting:

INSERT INTO foobar (foo, bar) VALUES ('EXAMPLE', 2);

我希望将 EXAMPLE 小写为示例，然后再执行。

I would want EXAMPLE to be lowercased to example before persisting.

推荐答案

该解决方案已经过测试并在Postgres 9.1中工作

我使用触发器来解决问题。

I used triggers to solve problem.

这里是完整代码，因此您可以粘贴到postgres中并自己尝试&在下面解释其工作原理

Here's the full code so you can paste into postgres and try it out yourself & explanation of how it works below

DROP TABLE foobar;
CREATE TABLE foobar (
foo text,
bar int
);

CREATE OR REPLACE FUNCTION lowecase_foo_on_insert() RETURNS trigger AS $lowecase_foo_on_insert$
    BEGIN        
        NEW.foo = LOWER(NEW.foo);
        RETURN NEW;
    END;
$lowecase_foo_on_insert$ LANGUAGE plpgsql;

CREATE TRIGGER lowecase_foo_on_insert_trigger BEFORE INSERT OR UPDATE ON foobar
    FOR EACH ROW EXECUTE PROCEDURE lowecase_foo_on_insert();

INSERT INTO foobar (foo, bar) VALUES ('LOWERCASE ME', 1);

SELECT * FROM foobar; //result 'lowercase me'

创建示例表：

CREATE TABLE foobar (
    foo text,
    bar int
);

创建将（foo）转换为小写的函数：

Create function that converts (foo) to lowercase:

CREATE OR REPLACE FUNCTION lowecase_foo_on_insert() RETURNS trigger AS $lowecase_foo_on_insert$
    BEGIN        
        NEW.foo = LOWER(NEW.foo);
        RETURN NEW;
    END;
$lowecase_foo_on_insert$ LANGUAGE plpgsql;

创建一个触发器，在插入之前将代码执行为小写foo：

Create a trigger that executes code to lowercase foo before insert:

CREATE TRIGGER lowecase_foo_on_insert_trigger BEFORE INSERT OR UPDATE ON foobar
    FOR EACH ROW EXECUTE PROCEDURE lowecase_foo_on_insert();

现在来测试我们的工作：

And now to test our work:

INSERT INTO foobar (foo, bar) VALUES ('LOWERCASE ME', 1);
SELECT * FROM foobar;

结果是，foo列中的唯一行现在设置为小写我

Result is that the only row in column foo is now set to 'lowercase me'

这篇关于创建Postgres规则或触发器以在插入时自动将列转换为小写或大写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！