product reference generator in pl/pgsql

gobai.sql

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: gobai; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA gobai;


SET search_path = gobai, pg_catalog;

--
-- Name: reference; Type: DOMAIN; Schema: gobai; Owner: -
--

CREATE DOMAIN reference AS character varying
	CONSTRAINT must_be_alphanum CHECK (((VALUE)::text ~ '^[A-Z0-9]{4,}$'::text));

--
-- Name: before_insert_on_product(); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION before_insert_on_product() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
  DECLARE
    reference VARCHAR;
  BEGIN
    -- slugification
    IF NEW.slug IS NULL THEN
      NEW.slug := slugify(NEW.name);
    END IF;

    -- reference building
   reference := generate_reference(NEW.tags);
   NEW.reference := reference||substring(to_char(nextval('product_reference_seq'), '0000') FROM 2);

  RETURN NEW;
END;
$$;


--
-- Name: before_insert_on_product_pack(); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION before_insert_on_product_pack() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF NOT is_composite FROM product p WHERE p.reference = NEW.parent_ref THEN
    RAISE 'Product % is not a composite product.', NEW.parent_ref USING ERRCODE = 'integrity_constraint_violation';
  END IF;

  RETURN NEW;
END
$$;


--
-- Name: before_insert_on_product_type(); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION before_insert_on_product_type() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
  BEGIN

  -- if there are all mandatory tags

  IF check_mandatory_reference(NEW.tags) THEN

    -- reference building

    NEW.reference := generate_reference(NEW.tags);

    RETURN NEW;
  END IF;

  RAISE 'Missing mandatory tags in "%".', array_to_string(NEW.tags, ', ') USING ERRCODE = 'integrity_constraint_violation';
END;
$$;


--
-- Name: before_insert_or_update_on_buyer(); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION before_insert_or_update_on_buyer() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    IF NEW.password = OLD.password THEN
      RETURN NEW;
    END IF;
  END IF;

  NEW.password := crypt(NEW.password, gen_salt('bf'));

  RETURN NEW;
END;
$$;


--
-- Name: check_mandatory_reference(public.ltree[]); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION check_mandatory_reference(tags public.ltree[]) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
BEGIN
  RETURN 
    array_agg(given_tags.tag) @> array_agg(mandatory_tags.tag) 
  FROM 
    (
      SELECT DISTINCT 
        subpath(v.tag, 0, 1) AS tag 
      FROM 
        variation v 
      WHERE 
        mandatory
    ) mandatory_tags, 
    (
      SELECT DISTINCT 
        subpath(v.tag, 0 ,1) AS tag 
      FROM 
        (
          SELECT 
            unnest(tags) AS tag
        ) v
    ) given_tags;
END;
$$;


--
-- Name: check_one_tag_per_category(public.ltree[]); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION check_one_tag_per_category(tags public.ltree[]) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
BEGIN
  RETURN NOT EXISTS
  (
    SELECT 
      tags1.tag AS category,
      count(*) AS category_count
   FROM 
   (
     SELECT 
      subpath(p.tag, 0, 1) AS tag
    FROM
      (
        SELECT 
          unnest(tags) AS tag
      ) p
   ) tags1
 GROUP BY 
   tags1.tag
 HAVING 
   count(*) > 1
 );
END;
$$;


--
-- Name: cut_nicely(character varying, integer); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION cut_nicely(my_string character varying, my_length integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
    DECLARE
        my_pointer INTEGER;
    BEGIN
        my_pointer := my_length;
        WHILE my_pointer < length(my_string) AND transliterate(substr(my_string, my_pointer, 1)) ~* '[a-z]' LOOP
            my_pointer := my_pointer + 1;
        END LOOP;

        RETURN substr(my_string, 1, my_pointer);
    END;
$$;


--
-- Name: generate_reference(public.ltree[]); Type: FUNCTION; Schema: gobai; Owner: -
--

CREATE FUNCTION generate_reference(tags public.ltree[]) RETURNS text
    LANGUAGE plpgsql
    AS $$
BEGIN
  RETURN
      array_to_string(array_agg(t.reference), '')
    FROM
      (
        SELECT
          v.reference,
          v.tag
        FROM
          variation v,
          (
            SELECT
              unnest(tags) AS tag
          ) p
        WHERE
            p.tag = v.tag
        ORDER BY
          v.rank ASC
      ) t
;
END;
$$;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: buyer; Type: TABLE; Schema: gobai; Owner: -; Tablespace: 
--

CREATE TABLE buyer (
    email character varying NOT NULL,
    password character varying NOT NULL,
    first_name character varying,
    last_name character varying,
    CONSTRAINT buyer_email_check CHECK (is_email(email))
);


--
-- Name: product; Type: TABLE; Schema: gobai; Owner: -; Tablespace: 
--

CREATE TABLE product (
    reference reference NOT NULL,
    description character varying NOT NULL,
    name character varying NOT NULL,
    slug character varying,
    tags public.ltree[] NOT NULL,
    price numeric(5,2) NOT NULL,
    stock integer DEFAULT 0 NOT NULL,
    image bytea,
    is_composite boolean DEFAULT false,
    CONSTRAINT check_positive_stock CHECK ((stock >= 0)),
    CONSTRAINT product_price_check CHECK ((price > (0)::numeric))
);


--
-- Name: product_pack; Type: TABLE; Schema: gobai; Owner: -; Tablespace: 
--

CREATE TABLE product_pack (
    parent_ref reference NOT NULL,
    children_ref reference NOT NULL
);


--
-- Name: product_reference_seq; Type: SEQUENCE; Schema: gobai; Owner: -
--

CREATE SEQUENCE product_reference_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: product_type; Type: TABLE; Schema: gobai; Owner: -; Tablespace: 
--

CREATE TABLE product_type (
    reference reference NOT NULL,
    designation character varying NOT NULL,
    tags public.ltree[] NOT NULL,
    price numeric(5,2) NOT NULL,
    image bytea,
    CONSTRAINT check_tags CHECK (((tags[1] OPERATOR(public.~) 'bijou.*'::public.lquery) AND (tags[2] OPERATOR(public.~) 'collection.*'::public.lquery))),
    CONSTRAINT product_type_price_check CHECK ((price > (0)::numeric))
);


--
-- Name: variation; Type: TABLE; Schema: gobai; Owner: -; Tablespace: 
--

CREATE TABLE variation (
    reference character(2) NOT NULL,
    tag public.ltree NOT NULL,
    mandatory boolean DEFAULT false,
    rank integer
);


--
-- Name: buyer_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: 
--

ALTER TABLE ONLY buyer
    ADD CONSTRAINT buyer_pkey PRIMARY KEY (email);


--
-- Name: product_pack_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: 
--

ALTER TABLE ONLY product_pack
    ADD CONSTRAINT product_pack_pkey PRIMARY KEY (parent_ref, children_ref);


--
-- Name: product_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: 
--

ALTER TABLE ONLY product
    ADD CONSTRAINT product_pkey PRIMARY KEY (reference);


--
-- Name: product_type_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: 
--

ALTER TABLE ONLY product_type
    ADD CONSTRAINT product_type_pkey PRIMARY KEY (reference);


--
-- Name: variation_pkey; Type: CONSTRAINT; Schema: gobai; Owner: -; Tablespace: 
--

ALTER TABLE ONLY variation
    ADD CONSTRAINT variation_pkey PRIMARY KEY (reference);


--
-- Name: before_insert_on_product_pack_trig; Type: TRIGGER; Schema: gobai; Owner: -
--

CREATE TRIGGER before_insert_on_product_pack_trig
    BEFORE INSERT ON product_pack
    FOR EACH ROW
    EXECUTE PROCEDURE before_insert_on_product_pack();


--
-- Name: before_insert_on_product_trig; Type: TRIGGER; Schema: gobai; Owner: -
--

CREATE TRIGGER before_insert_on_product_trig
    BEFORE INSERT ON product
    FOR EACH ROW
    EXECUTE PROCEDURE before_insert_on_product();


--
-- Name: before_insert_on_product_type_trig; Type: TRIGGER; Schema: gobai; Owner: -
--

CREATE TRIGGER before_insert_on_product_type_trig
    BEFORE INSERT ON product_type
    FOR EACH ROW
    EXECUTE PROCEDURE before_insert_on_product_type();


--
-- Name: before_insert_or_update_on_buyer_trig; Type: TRIGGER; Schema: gobai; Owner: -
--

CREATE TRIGGER before_insert_or_update_on_buyer_trig
    BEFORE INSERT OR UPDATE ON buyer
    FOR EACH ROW
    EXECUTE PROCEDURE before_insert_or_update_on_buyer();


--
-- Name: product_pack_children_ref_fkey; Type: FK CONSTRAINT; Schema: gobai; Owner: -
--

ALTER TABLE ONLY product_pack
    ADD CONSTRAINT product_pack_children_ref_fkey FOREIGN KEY (children_ref) REFERENCES product(reference) ON DELETE RESTRICT;


--
-- Name: product_pack_parent_ref_fkey; Type: FK CONSTRAINT; Schema: gobai; Owner: -
--

ALTER TABLE ONLY product_pack
    ADD CONSTRAINT product_pack_parent_ref_fkey FOREIGN KEY (parent_ref) REFERENCES product(reference) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--