列中定义的 PostgreSQL 函数

Question

我一直试图在网上找到这个，但我是 postgresql 的新手，我可能不太了解快速过滤出搜索结果..

我声明了 2 个表，country 和 person，其中每个 person 都有一个 country:

CREATE SEQUENCE country_id_seq;
CREATE TABLE country
(
  id integer NOT NULL DEFAULT nextval('country_id_seq'),
  name character varying(40) NOT NULL,
  validator regproc,
  CONSTRAINT country_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE person_id_seq;
CREATE TABLE person
(
  id integer NOT NULL DEFAULT nextval('person_id_seq'),
  first_name character varying(40) NOT NULL,
  country_id integer NOT NULL,
  CONSTRAINT person_pkey PRIMARY KEY (id),
  CONSTRAINT person_fk_country_id FOREIGN KEY (country_id)
      REFERENCES country (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
);

每个 country 都有一个 validator，这是我添加以下内容的函数:

CREATE FUNCTION validator_PT(name text) RETURNS boolean AS $$
BEGIN
    RETURN FALSE;
END
$$
LANGUAGE plpgsql;

INSERT INTO country(id, name,validator) VALUES(1, 'Portugal','validator_PT');
INSERT INTO country(id, name) VALUES(2, 'Italia');

现在我想在 person 上创建一个触发器函数，它将使用 country[person.country_id].validor 来验证 person.first_name

CREATE OR REPLACE FUNCTION trigger_person_validate_name() RETURNS trigger AS $$
DECLARE
  validator regproc;
BEGIN
  IF NEW.country_id IS NOT NULL THEN
    validator := validator FROM country WHERE id = NEW.country_id;

    IF validator IS NOT NULL THEN
      IF NOT validator(NEW.first_name) THEN
        RAISE EXCEPTION 'Invalid first name: %', NEW.first_name;
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_person_validate_name_insert
  BEFORE INSERT
  ON person
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_person_validate_name();

简而言之，我想调用在 country.validator 中配置的函数，但是，按照我正在做的，我得到:

INSERT INTO person(first_name, country_id) VALUES('john',1)

ERROR: function validator(character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

因此它尝试执行 validator 函数而不是 validator_PT。我也尝试过使用 EXECUTE:

DECLARE
    validator regproc;
    valid boolean;
BEGIN
  ...
  EXECUTE 'SELECT $1($2)' INTO valid USING validator, NEW.first_name;
  IF NOT valid THEN
  ...

但它失败了:

ERROR: syntax error at or near "("

引用 EXECUTE 行。这样做的正确方法是什么？ (当然，如果需要，我可以更改表的架构)

我正在使用 PostgreSQL 9.3

Answer 1

我注意到 ( IRL ) 我为此滥用了 EXECUTE USING 并且应该通过直接连接(至少是函数名称)像 here比如:

DECLARE
    validator regproc;
    valid boolean;
BEGIN
  ...
  EXECUTE 'SELECT ' || validator || '($1)' INTO valid USING NEW.first_name;
  IF NOT valid THEN
  ...

问题已解决(尽管知道是否有任何方法可以在没有 EXECUTE 的情况下实现这一目标仍然很高兴)