text: -- Generated by Ora2Pg, the Oracle database Schema converter, version 17.4
  -- Copyright 2000-2016 Gilles DAROLD. All rights reserved.
  -- DATASOURCE: dbi:Oracle:host=sgd-nex2-db.stanford.edu;sid=SGD
  
  SET client_encoding TO 'UTF8';
  
  \set ON_ERROR_STOP ON
  
  --
  -- Insert into deletelog table whenever a row is deleted from a table
  --
  CREATE OR REPLACE FUNCTION insertdeletelog (p_table text, p_key bigint, p_row text, p_user name)  RETURNS VOID AS $body$
  BEGIN
  
      INSERT INTO nex.deletelog
          (tab_name, primary_key, deleted_row, created_by)
      VALUES
          (upper(p_table), p_key, p_row, upper(p_user));
  
  END;
  
  $body$ LANGUAGE PLPGSQL
         SECURITY DEFINER
         SET search_path = nex, pg_temp;
  GRANT EXECUTE on FUNCTION insertdeletelog (p_table text, p_key bigint, p_row text, p_user name) to CURATOR;
  REVOKE ALL ON FUNCTION insertdeletelog (p_table text, p_key bigint, p_row text, p_user name) FROM PUBLIC;
  
  
  --
  -- Insert into updatelog table whenever a row is updated in a table
  --
  CREATE OR REPLACE FUNCTION insertupdatelog (p_table text, p_column text, p_key bigint, p_old text, p_new text, p_user name)  RETURNS VOID AS $body$
  BEGIN
  
      INSERT INTO nex.updatelog
          (tab_name, col_name, primary_key, old_value, new_value, created_by)
      VALUES
          (upper(p_table), upper(p_column), p_key, p_old, p_new, upper(p_user));
  
  END;
  
  $body$ LANGUAGE PLPGSQL
         SECURITY DEFINER
         SET search_path = nex, pg_temp;
  GRANT EXECUTE on FUNCTION insertupdatelog (p_table text, p_column text, p_key bigint, p_old text, p_new text, p_user name) to CURATOR;
  REVOKE ALL ON FUNCTION insertupdatelog (p_table text, p_column text, p_key bigint, p_old text, p_new text, p_user name) FROM PUBLIC;
  
  
  --
  -- Automatic insert of only NEW values into the arch_locuschange table for gene_names and qualifiers
  --
  CREATE OR REPLACE FUNCTION insertnewonlylocuschange (p_dbentityId bigint, p_sourceName text, p_changeType text, p_new text, p_user name) RETURNS VOID AS $body$
  DECLARE
  
     v_source_id   nex.arch_locuschange.source_id%TYPE;
     v_added_date nex.arch_locuschange.date_added_to_database%TYPE;
  
  BEGIN
  
      SELECT source_id INTO v_source_id
      FROM nex.source
      WHERE display_name = p_sourceName;
  
      SELECT LOCALTIMESTAMP INTO v_added_date;
  
      INSERT INTO nex.arch_locuschange
          (dbentity_id, source_id, change_type, new_value, date_added_to_database, added_by)
      VALUES
          (p_dbentityID, v_source_id, p_changeType, p_new, v_added_date, upper(p_user));
  
  END;
  
  $body$ LANGUAGE PLPGSQL
         SECURITY DEFINER
         SET search_path = nex, pg_temp;
  GRANT EXECUTE on FUNCTION insertnewonlylocuschange (p_dbentityId bigint, p_sourceName text, p_changeType text, p_new text, p_user name) to CURATOR;
  REVOKE ALL ON FUNCTION insertnewonlylocuschange (p_dbentityId bigint, p_sourceName text, p_changeType text, p_new text, p_user name) FROM PUBLIC;
  
  
  --
  -- Automatic insert NEW and OLD values into the arch_locuschange table for gene_names and qualifiers
  --
  CREATE OR REPLACE FUNCTION insertnewoldlocuschange (p_dbentityId bigint, p_sourceName text, p_changeType text, p_old text, p_new text, p_user name) RETURNS VOID AS $body$
  DECLARE
  
     v_source_id   nex.arch_locuschange.source_id%TYPE;
     v_added_date nex.arch_locuschange.date_added_to_database%TYPE;
  
  BEGIN
  
      SELECT source_id INTO v_source_id
      FROM nex.source
      WHERE display_name = p_sourceName;
  
      SELECT LOCALTIMESTAMP INTO v_added_date;
  
      INSERT INTO nex.arch_locuschange
          (dbentity_id, source_id, change_type, old_value, new_value, date_added_to_database, added_by)
      VALUES
          (p_dbentityID, v_source_id, p_changeType, p_old, p_new, v_added_date, upper(p_user));
  
  END;
  
  $body$ LANGUAGE PLPGSQL
         SECURITY DEFINER
         SET search_path = nex, pg_temp;
  GRANT EXECUTE on FUNCTION insertnewoldlocuschange (p_dbentityId bigint, p_sourceName text, p_changeType text, p_old text, p_new text, p_user name) to CURATOR;
  REVOKE ALL ON FUNCTION insertnewoldlocuschange (p_dbentityId bigint, p_sourceName text, p_changeType text, p_old text, p_new text, p_user name) FROM PUBLIC;
  
  
  --
  -- Automatic insert into SGDID table
  --
  CREATE OR REPLACE FUNCTION insertsgdid (p_sgdid varchar, p_source text, p_sgdidClass varchar, p_sgdidStatus text, p_user name) RETURNS VOID AS $body$
  DECLARE
  
      v_source_id   nex.sgdid.source_id%TYPE;
      v_objurl      nex.sgdid.obj_url%TYPE;
  
  BEGIN
  
      select source_id INTO v_source_id
      from nex.source
      where display_name = p_source;
  
      v_objurl := CONCAT('/sgdid/', p_sgdid);
  
      INSERT INTO nex.sgdid
          (format_name, display_name, obj_url, source_id, subclass, sgdid_status, created_by)
      VALUES
          (p_sgdid, p_sgdid, v_objurl, v_source_id, p_sgdidClass, p_sgdidStatus, upper(p_user));
  
  END;
  
  $body$ LANGUAGE PLPGSQL
         SECURITY DEFINER
         SET search_path = nex, pg_temp;
  GRANT EXECUTE on FUNCTION insertsgdid (p_sgdid varchar, p_source text, p_sgdidClass varchar, p_sgdidStatus text, p_user name) to CURATOR;
  REVOKE ALL ON FUNCTION insertsgdid (p_sgdid varchar, p_source text, p_sgdidClass varchar, p_sgdidStatus text, p_user name) FROM PUBLIC;      
  