觸發函式 - PostgreSQL 9.2

想法：

1 - 客戶可以在 users.code 列中添加任何值 2 - 如果客戶不向其中插入數據，則觸發器/功能必須完成這項工作

• 我能夠做到這一點，使用這個 SQL：

   CREATE OR REPLACE FUNCTION tf_users_update_code_column()
      RETURNS trigger AS $$

          BEGIN

            IF NEW.company_id = 1 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c1_users_code_seq');

            ELSEIF NEW.company_id = 2 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c2_users_code_seq');

            ELSEIF NEW.company_id = 3 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c3_users_code_seq');

            ELSEIF NEW.company_id = 4 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c4_users_code_seq');

            ELSEIF NEW.company_id = 5 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c5_users_code_seq');

            ELSEIF NEW.company_id = 6 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c6_users_code_seq');

            ELSEIF NEW.company_id = 7 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c7_users_code_seq');

            ELSEIF NEW.company_id = 8 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c8_users_code_seq');

            ELSEIF NEW.company_id = 9 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c9_users_code_seq');

            ELSEIF NEW.company_id = 10 AND NEW.code IS NULL THEN
            NEW.code = NEXTVAL('c10_users_code_seq');

          END IF;

          return NEW;

          END

    $$  LANGUAGE plpgsql;

    2 - Creating the sequences....

    CREATE SEQUENCE c1_users_code_seq  
    INCREMENT 1  
    MINVALUE 1
    MAXVALUE 9223372036854775807  
    START 1000;
    CACHE 1;

    CREATE SEQUENCE c2_users_code_seq  
    INCREMENT 1  
    MINVALUE 1
    MAXVALUE 9223372036854775807  
    START 1000;
    CACHE 1;

    CREATE SEQUENCE c3_users_code_seq  
    INCREMENT 1  
    MINVALUE 1
    MAXVALUE 9223372036854775807  
    START 1000;
    CACHE 1;

    CREATE SEQUENCE c4_users_code_seq  
    INCREMENT 1  
    MINVALUE 1
    MAXVALUE 9223372036854775807  
    START 1000;
    CACHE 1;


    ... [etc] ...

    3 - Creating the TRIGGER

    CREATE TRIGGER t_users_update_code_column
      BEFORE INSERT
      ON users
      FOR EACH ROW
      EXECUTE PROCEDURE tf_users_update_code_column();

• 但是因為會有幾千家公司，我不可能有幾千個序列
• 這就是我寫下面程式碼的原因：

 CREATE TABLE public.company_seqs
    (company_id BIGINT NOT NULL,
    last_seq BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
    );

    CREATE OR REPLACE FUNCTION users_code_seq() RETURNS trigger AS $$

    DECLARE
    last_seq BIGINT;
    company_id BIGINT;
    code character varying;

    BEGIN
            IF (TG_OP = 'INSERT') THEN
                    UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id;

                    ELSEIF NEW.code IS NULL THEN
                    SELECT last_seq INTO NEW.code FROM public.company_seqs WHERE company_id = NEW.company_id;

            END IF;

            RETURN new;

            END;

    $$ LANGUAGE plpgsql;

    CREATE TRIGGER tf_users_code_seq
       BEFORE INSERT
       ON public.users
       FOR EACH ROW
       EXECUTE PROCEDURE users_code_seq();

將數據插入 public.users 表時：

INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (671,'test1@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01','default','1');
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'test2@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1');
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (673,'test3@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1');
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (674,'test4@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01','something','1');

我收到以下錯誤：

BEGIN
psql:1.sql:3: ERROR:  column reference "last_seq" is ambiguous
LINE 1: UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHE...
                                                   ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id
CONTEXT:  PL/pgSQL function users_code_seq() line 10 at SQL statement

我錯過了什麼？

最終程式碼：（有效）

ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT 1000;

COMMIT TRANSACTION;


BEGIN;
-- Creating the function
CREATE OR REPLACE FUNCTION users_code_seq()
  RETURNS "trigger" AS $$

DECLARE code character varying;

BEGIN

-- if it's an insert, then we update the client_code_increment column value to +1
       IF (TG_OP = 'INSERT') THEN
       UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.company_id;

       END IF;
-- IF the customer didn't provide a code value, we insert the next available from companies.client_code_increment
       IF NEW.code IS NULL THEN
       SELECT client_code_increment INTO NEW.code FROM public.companies as c WHERE c.id = NEW.company_id ORDER BY client_code_increment DESC;

       END IF;

       RETURN NEW;

END;

$$ LANGUAGE plpgsql;

-- Creating the trigger
CREATE TRIGGER tf_users_code_seq
  BEFORE INSERT
  ON public.users
  FOR EACH ROW
  EXECUTE PROCEDURE users_code_seq();


COMMIT TRANSACTION;

引用自：https://dba.stackexchange.com/questions/135998