CREATE OR REPLACE FUNCTION MIG_ORA_EXT.userenv
/* This function is used to retrieve information about the current session. 
   Currently the "sid", "instance" and "client_info" are supported.
*/
	( i_session_attr 	IN		VARCHAR(32) ) 
RETURNS VARCHAR(64)
AS
$$
DECLARE
     l_session_attr 			VARCHAR(32);
	 o_session_attr_value		VARCHAR(64);
BEGIN
	
	l_session_attr	:= TRIM(i_session_attr);

	IF l_session_attr IS NULL
	THEN
		RAISE EXCEPTION 'Empty string/NULL is an invalid USERENV parameter';
	ELSE

		l_session_attr	:= UPPER(l_session_attr);
		IF l_session_attr = 'SID'
		-- if session id is required
		THEN
			o_session_attr_value := pg_catalog.pg_backend_pid();

		ELSIF l_session_attr = 'INSTANCE'
		-- if instance id is required
		THEN
			SELECT n.node_id 
			  INTO o_session_attr_value
			  FROM pg_catalog.pgxc_node n, pg_catalog.pg_settings s
			 WHERE s.name 		= 'pgxc_node_name' 
			   AND n.node_name 	= s.setting;

		ELSIF l_session_attr = 'CLIENT_INFO'
		-- it returns the session's application_name, which is set using set application_name='<<CLIENT APPLICATION NAME>>'
		THEN
			SELECT s.application_name 
			  INTO o_session_attr_value
			  FROM pg_catalog.pg_stat_activity s
			 WHERE s.pid 	= pg_catalog.pg_backend_pid();

		ELSIF l_session_attr IN ( 'ENTRYID', 'ISDBA', 'LANG', 'LANGUAGE', 'SESSIONID', 'TERMINAL' )
		-- if other valid Oracle parameter is passed
		THEN
			RAISE EXCEPTION '% is an unsupported USERENV parameter', i_session_attr;

		ELSE
		-- if invalid parameter is passed
			RAISE EXCEPTION '% is an invalid USERENV parameter', i_session_attr;
		END IF;

	END IF;
	
	RETURN o_session_attr_value;
END;
$$ 
LANGUAGE plpgsql;


--------- Support of sys_guid
CREATE SEQUENCE MIG_ORA_EXT.seq_sys_guid
INCREMENT 1
MINVALUE 1
CYCLE;

CREATE OR REPLACE FUNCTION MIG_ORA_EXT.sys_guid
/* It returns unique value across the Gauss servers
   The current coordinator_ip & port, session processid and number of seconds (with milliseconds) 
   and sequence are used to form the uniqueness. */
RETURNS VARCHAR(64)
AS
$$
DECLARE
     l_pid	 					BIGINT;
	 l_coordinator_ip			VARCHAR(15);
	 l_coordinator_port			INT;
	 l_seconds					NUMERIC;
	 l_seq_value				BIGINT;
	 l_gunique_value_wo_md5		VARCHAR(64);
	 o_gunique_value			VARCHAR(64);
BEGIN

	-- Find the coordinator IP address, where it is connected
	SELECT setting
	  INTO l_coordinator_ip
	  FROM pg_catalog.pg_settings s
	 WHERE s.name   = 'local_bind_address';
	
	-- Find the coordinator port, where it is connected
	SELECT setting
	  INTO l_coordinator_port
	  FROM pg_catalog.pg_settings s
	 WHERE s.name   = 'port';

	-- Process ID of the server process attached to the current session
	l_pid		:= pg_catalog.pg_backend_pid();
	
	-- For current timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative)
	l_seconds	:= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP);
	
	l_seq_value  := NEXTVAL('MIG_ORA_EXT.seq_sys_guid');
	
	-- Concatenating all the variables
	l_gunique_value_wo_md5	:= l_coordinator_ip || '-' || l_coordinator_port || '-' || l_pid 
							   || '-' || l_seconds || '-' || l_seq_value;
	
	o_gunique_value		    := MD5(l_gunique_value_wo_md5);
	
	RETURN o_gunique_value;
END;
$$ 
LANGUAGE plpgsql;
