qt5-legatus-legacy/Lts/Tests/DatabaseInitialisation/init_db_procedures.sql

-- # $Id$
-- # $HeadURL$
-- # $Author$

------------------------
-- Create user procedure
------------------------
DROP FUNCTION IF EXISTS create_user_if_not_exists(text);
DROP FUNCTION IF EXISTS create_user_if_not_exists(text, text);
CREATE OR REPLACE FUNCTION public.create_user_if_not_exists(rolename text, passw text default 'ets11de') RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_roles WHERE rolname = rolename) THEN
        EXECUTE format('CREATE USER %s WITH PASSWORD ''%s''', rolename, passw);
        EXECUTE format('GRANT ets_ro TO %s', rolename);
        RETURN 'CREATE USER';
    ELSE
        RETURN format('USER ''%I'' ALREADY EXISTS', rolename);
    END IF;
END;
$$
LANGUAGE plpgsql;

---------------------------------
--- Revoke user grants in schema
---------------------------------
CREATE OR REPLACE FUNCTION public.revoke_user_if_exists(rolename text, schema text) RETURNS TEXT AS
$$
BEGIN
    IF EXISTS (SELECT * FROM pg_roles WHERE rolname = rolename) THEN
        EXECUTE format('REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA %s FROM %s CASCADE;', schema, rolename);
        EXECUTE format('REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %s FROM %s CASCADE;', schema, rolename);
        EXECUTE format('REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA %s FROM %s CASCADE;', schema, rolename);
        EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA %s REVOKE ALL ON TABLES FROM %s;', schema, rolename);
        EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE ets_exm IN SCHEMA %s REVOKE ALL ON TABLES FROM %s;', schema, rolename);
        EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA %s REVOKE ALL ON FUNCTIONS FROM %s;', schema, rolename);
        EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE ets_exm IN SCHEMA %s REVOKE ALL ON FUNCTIONS FROM %s;', schema, rolename);
        EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA %s REVOKE ALL ON SEQUENCES FROM %s;', schema, rolename);
        EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE ets_exm IN SCHEMA %s REVOKE ALL ON SEQUENCES FROM %s;', schema, rolename);
        RETURN 'USER REVOKED';
    ELSE
        RETURN format('USER ''%I'' DOES NOT EXIST', rolename);
    END IF;
END;
$$
LANGUAGE plpgsql;

-------------------------
-- Import bytea procedure
-------------------------
CREATE OR REPLACE FUNCTION public.bytea_import(p_path text, OUT p_result bytea) RETURNS bytea AS
$$
  DECLARE
  l_oid oid;
  r record;
BEGIN
   p_result := '';
   SELECT lo_import(p_path) INTO l_oid;
   for r in ( SELECT data FROM pg_largeobject WHERE loid = l_oid ORDER BY pageno ) loop
     p_result = p_result || r.data;
   end loop;
   perform lo_unlink(l_oid);
END;
$$
LANGUAGE plpgsql;

---------------------------------------
-- Check if constraint exists procedure
---------------------------------------
CREATE OR REPLACE FUNCTION public.exists_constraint(constraintName text, dbSchema text, constraintType char) RETURNS bool AS
$$
DECLARE
  exist_constraint "bool";
BEGIN
   EXECUTE 'SELECT COUNT(*)
            FROM   pg_class     c
            JOIN   pg_namespace n ON n.oid = c.relnamespace
            WHERE  c.relname = $1
            AND    n.nspname = $2
            AND    c.relkind = $3'
   INTO exist_constraint
   USING constraintName, dbSchema, constraintType;
   IF exist_constraint THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;
$$
LANGUAGE plpgsql;

-------------------------------------
-- Check if sequence exists procedure
-------------------------------------
CREATE OR REPLACE FUNCTION public.exists_sequence(sequenceName text, dbSchema text) RETURNS bool AS
$$
DECLARE
   exist_sequence "char";
BEGIN
   EXECUTE 'SELECT c.relkind
            FROM   pg_class     c
            JOIN   pg_namespace n ON n.oid = c.relnamespace
            WHERE  c.relname = $1
            AND    n.nspname = $2
            AND    c.relkind = ''S'''
   INTO exist_sequence
   USING sequenceName, dbSchema;
   IF exist_sequence IS NOT NULL THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;
$$
LANGUAGE plpgsql;

-------------------------------------
-- Reduce array dimension 1 procedure
-------------------------------------
CREATE OR REPLACE FUNCTION public.reduce_dim1(anyarray)
RETURNS SETOF int AS
$function$
DECLARE
  s $1%TYPE;
BEGIN
  IF cardinality($1) > 0 THEN
    FOREACH s SLICE 1 IN ARRAY $1 LOOP
      RETURN NEXT s[1];
    END LOOP;
  END IF;
  RETURN;
END;
$function$
LANGUAGE plpgsql IMMUTABLE;

-------------------------------------
-- Reduce array dimension 2 procedure
-------------------------------------
CREATE OR REPLACE FUNCTION public.reduce_dim2(anyarray)
RETURNS SETOF int AS
$function$
DECLARE
    s $1%TYPE;
BEGIN
  IF cardinality($1) > 0 THEN
    FOREACH s SLICE 1 IN ARRAY $1 LOOP
      RETURN NEXT s[2];
    END LOOP;
  END IF;
  RETURN;
END;
$function$
LANGUAGE plpgsql IMMUTABLE;

--------------------------------------
-- GRANTS
-------------------------------------
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ets_ro;
GRANT USAGE ON SCHEMA public TO ets_ro;