Postgresql – Trigger function – PostgreSQL 9.2

functionsplpgsqlpostgresqlsequencetrigger

The idea:

1 – The customer can add any value into users.code column
2 – If the customer does not insert data into it, the trigger/funciton has to do the job

I am able to do that, using this 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();

But because there will be thousands of companies, I can't have thousands of sequences
That's why I'm writing the code below:

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

When inserting data into the public.users table:

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

I got the following error:

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

What am I missing?

Best Answer

Final code: (It works)

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;

Related Solutions

Sql-server – Launching a python script from an insert trigger

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';

As an aside, you should state in your question that you aware that this is a very bad thing^TM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?

PostgreSQL trigger function that selects from %current% table

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want:

BEGIN
    EXECUTE format('SELECT colname FROM %I', TG_RELID)
END;

BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME)
END;

(Of course these won't work as-is, since the SELECT has no destination for the data. You have to use EXECUTE format(..) INTO ... to store the result into a DECLAREd variable), e.g.

DECLARE
    _colvar integer;
BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO _colvar;
    RAISE NOTICE 'colname value was %',_colvar;
END;

Best Answer

Related Solutions

Sql-server – Launching a python script from an insert trigger

PostgreSQL trigger function that selects from %current% table

Related Question