--
-- PostgreSQL database dump
--
-- Dumped from database version 9.5.5
-- Dumped by pg_dump version 9.5.5
--
-- Name: add_group_role(character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION add_group_role(group_name character varying, role_name character varying) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
groupid bigint;
roleid bigint;
relationcount bigint;
BEGIN
SELECT id INTO groupid FROM qcadoosecurity_group WHERE identifier = group_name;
IF groupid is null THEN
RAISE EXCEPTION 'Group(%s) not found', group_name;
END IF;
SELECT id INTO roleid FROM qcadoosecurity_role WHERE identifier = role_name;
IF roleid is null THEN
RAISE EXCEPTION 'Role(%) not found', role_name;
END IF;
SELECT count(*) INTO relationcount FROM jointable_group_role WHERE group_id = groupid and role_id = roleid;
IF relationcount = 0 THEN
INSERT INTO jointable_group_role (group_id, role_id) VALUES (groupid, roleid);
END IF;
END;
$$;
ALTER FUNCTION public.add_group_role(group_name character varying, role_name character varying) OWNER TO postgres;
--
-- Name: add_group_role_by_id(bigint, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION add_group_role_by_id(groupid bigint, role_name character varying) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
roleid bigint;
relationcount bigint;
BEGIN
IF groupid is null THEN
RAISE EXCEPTION 'Group(%s) not found', groupid;
END IF;
SELECT id INTO roleid FROM qcadoosecurity_role WHERE identifier = role_name;
IF roleid is null THEN
RAISE EXCEPTION 'Role(%) not found', role_name;
END IF;
SELECT count(*) INTO relationcount FROM jointable_group_role WHERE group_id = groupid and role_id = roleid;
IF relationcount = 0 THEN
INSERT INTO jointable_group_role (group_id, role_id) VALUES (groupid, roleid);
END IF;
END;
$$;
ALTER FUNCTION public.add_group_role_by_id(groupid bigint, role_name character varying) OWNER TO postgres;
--
-- Name: add_role(character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION add_role(role_name character varying, description character varying) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
relationcount bigint;
BEGIN
SELECT count(*) INTO relationcount FROM qcadoosecurity_role WHERE identifier = role_name;
IF relationcount = 0 THEN
INSERT INTO qcadoosecurity_role (identifier, description) VALUES (role_name, description);
END IF;
END;
$$;
ALTER FUNCTION public.add_role(role_name character varying, description character varying) OWNER TO postgres;
--
-- Name: add_sequences(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION add_sequences() RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables p
INNER JOIN information_schema.columns c ON p.tablename = c.table_name
WHERE c.table_schema = 'public' AND p.schemaname = 'public' AND c.column_name = 'id' AND data_type = 'bigint'
LOOP
IF NOT EXISTS (SELECT 0 FROM pg_class WHERE relname = substring('' || quote_ident(row.tablename) || '_id_seq' FROM 0 FOR 64)) THEN
EXECUTE 'CREATE SEQUENCE ' || quote_ident(row.tablename) || '_id_seq;';
EXECUTE 'ALTER TABLE ' || quote_ident(row.tablename) || ' ALTER COLUMN id SET DEFAULT nextval(''' || quote_ident(row.tablename) || '_id_seq'');';
EXECUTE 'ALTER SEQUENCE ' || quote_ident(row.tablename) || '_id_seq OWNED BY ' || quote_ident(row.tablename) || '.id';
EXECUTE 'WITH mx AS (SELECT max(id)+1 AS mx FROM ' || quote_ident(row.tablename) || ') SELECT setval( ''' || quote_ident(row.tablename) || '_id_seq'' , mx.mx) FROM mx';
END IF;
END LOOP;
FOR row IN SELECT viewname FROM pg_views p
INNER JOIN information_schema.columns c ON p.viewname = c.table_name
WHERE c.table_schema = 'public' AND p.schemaname = 'public' AND c.column_name = 'id' AND data_type = 'bigint'
LOOP
IF NOT EXISTS (SELECT 0 FROM pg_class WHERE relname = substring('' || quote_ident(row.viewname) || '_id_seq' FROM 0 FOR 64)) THEN
EXECUTE 'CREATE SEQUENCE ' || quote_ident(row.viewname) || '_id_seq;';
END IF;
END LOOP;
END;
$$;
ALTER FUNCTION public.add_sequences() OWNER TO postgres;
--
-- Name: f_add_col(regclass, text, regtype); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION f_add_col(_tbl regclass, _col text, _type regtype) RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_attribute
WHERE attrelid = _tbl
AND attname = lower(_col)
AND NOT attisdropped) THEN
RETURN FALSE;
ELSE
EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s', _tbl, lower(_col), _type);
RETURN TRUE;
END IF;
END
$$;
ALTER FUNCTION public.f_add_col(_tbl regclass, _col text, _type regtype) OWNER TO postgres;
--
-- Name: f_add_col_default(regclass, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION f_add_col_default(_tbl regclass, _col text, _type text, _default text) RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_attribute
WHERE attrelid = _tbl
AND attname = lower(_col)
AND NOT attisdropped) THEN
RETURN FALSE;
ELSE
EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s DEFAULT %s', _tbl, lower(_col), _type, _default);
RETURN TRUE;
END IF;
END
$$;
ALTER FUNCTION public.f_add_col_default(_tbl regclass, _col text, _type text, _default text) OWNER TO postgres;
--
-- Name: generate_and_set_assignmenttoshift_externalnumber_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION generate_and_set_assignmenttoshift_externalnumber_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.externalnumber := generate_assignmenttoshift_externalnumber();
return NEW;
END;
$$;
ALTER FUNCTION public.generate_and_set_assignmenttoshift_externalnumber_trigger() OWNER TO postgres;
--
-- Name: generate_and_set_confectionprotocol_externalnumber_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION generate_and_set_confectionprotocol_externalnumber_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.externalnumber := generate_confectionprotocol_externalnumber();
return NEW;
END;
$$;
ALTER FUNCTION public.generate_and_set_confectionprotocol_externalnumber_trigger() OWNER TO postgres;
--
-- Name: generate_and_set_document_number_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION generate_and_set_document_number_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.number := generate_document_number(NEW.number);
IF NEW.name is null THEN
NEW.name := NEW.number;
END IF;
return NEW;
END;
$$;
ALTER FUNCTION public.generate_and_set_document_number_trigger() OWNER TO postgres;
--
-- Name: generate_and_set_extrusionprotocol_externalnumber_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION generate_and_set_extrusionprotocol_externalnumber_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.externalnumber := generate_extrusionprotocol_externalnumber();
return NEW;
END;
$$;
ALTER FUNCTION public.generate_and_set_extrusionprotocol_externalnumber_trigger() OWNER TO postgres;
--
-- Name: generate_and_set_maintenanceevent_number_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION generate_and_set_maintenanceevent_number_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.number := generate_mainten
