Re: Function PostgreSQL 9.2 - Mailing list pgsql-general

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;