CREATE OR REPLACE PROCEDURE mig_ntz_ext.mig_fn_ntz_external_table
/* This function is used to address the external table (unload data) feature
   of Netezza. Below shows the parameter mapping:
	  Location 			          => location 
      DELIMITER '16' (ASCII code) => delimiter (CHAR value)
      NULLVALUE ''                => null
      Y2BASE 2000                 => unsupported
      ENCODING 'internal'         => encoding
      REMOTESOURCE 'ODBC'         => unsupported
      ESCAPECHAR '\'              => escape
      INCLUDEZEROSECONDS 'TRUE'   => unsupported
      Includeheader 'TRUE'        => header
  This feature is addressed using Gauss200 Foreign table; It will be created using
  LIKE operator on the table, which is created based on SELECT stt provided.
*/
	 ( i_ftbl_name				VARCHAR
	 , i_select_qry				VARCHAR
	 , i_ftbl_location			VARCHAR
	 , i_ftbl_ascii_delimiter	VARCHAR 
	 , i_ftbl_nullval			VARCHAR 
	 , i_ftbl_encoding			VARCHAR 
	 , i_ftbl_escapechar		VARCHAR 
	 , i_ftbl_inc_header		VARCHAR )
AS
	lv_tabname_for_etab    		 VARCHAR(32);
	lv_ftbl_chr_delimiter		 VARCHAR(8);
	lv_ftbl_escape_str			 VARCHAR(64);
	lv_dynamic_sql				 TEXT;
BEGIN
	SET client_min_messages=WARNING;
	-- random table name for unlogged table and foreign table (if not provided)
	lv_tabname_for_etab   := 'mig_tab_for_extnltab_' || TRUNC(DBMS_RANDOM.VALUE(0,999999));
	i_ftbl_name			  := NVL(i_ftbl_name, lv_tabname_for_etab||'_ft');
	
	-- ASCII code to CHAR conversion
	lv_ftbl_chr_delimiter := CHR(i_ftbl_ascii_delimiter);

	IF TRIM(i_ftbl_nullval) IS NULL THEN
		i_ftbl_nullval := '';
	END IF;
	
	-- if the input encoding is internal, it will be treated as default encoding (client encoding)
	IF lower(TRIM(i_ftbl_encoding)) IN ('internal', '') OR i_ftbl_encoding IS NULL THEN
	   lv_ftbl_escape_str	 := '';
	ELSE
	   lv_ftbl_escape_str	 := ', ENCODING ''' || i_ftbl_encoding || '''';
	END IF;

	-- if the escape char is not provided, the default escape char (") will be used.
	IF TRIM(i_ftbl_escapechar) IS NULL THEN
		i_ftbl_escapechar := '"';
	END IF;
	
	-- if the includeheader is not provided, the default value (FALSE) will be used.
	IF TRIM(i_ftbl_inc_header) IS NULL THEN
		i_ftbl_inc_header := 'FALSE';
	END IF;
	
	/* FOREIGN table can be created by specifying column list with data type or LIKE another table.
	   The column list with data type is very complex since SELECT list can contain any kind of column expressions.
	   It is decided to use LIKE operator. Since FOREIGN table does not support TEMP table in LIKE operator, 
	   UNLOGGED TABLE is used.	*/
	lv_dynamic_sql	:= 'CREATE UNLOGGED TABLE ' || lv_tabname_for_etab ||
						' AS ' || i_select_qry ||
						' WITH NO DATA';
	EXECUTE IMMEDIATE lv_dynamic_sql;
	
	lv_dynamic_sql	:= 'CREATE FOREIGN TABLE ' || i_ftbl_name || ' ( LIKE ' || lv_tabname_for_etab || ' ) 
						SERVER gsmpp_server
						OPTIONS ( LOCATION ''' || i_ftbl_location || '''
								, FORMAT ''CSV'', DELIMITER ''' || lv_ftbl_chr_delimiter || '''
								, NULL ''' || i_ftbl_nullval || ''''
						--		, ' || 'ENCODING ''' || i_ftbl_encoding || '''
								|| lv_ftbl_escape_str || '
								, ' || 'ESCAPE ''' || i_ftbl_escapechar || '''
								, HEADER ''' || i_ftbl_inc_header || ''' ) 
						WRITE ONLY';
	EXECUTE IMMEDIATE lv_dynamic_sql;
	
	lv_dynamic_sql	:= 'DROP TABLE ' || lv_tabname_for_etab;
	EXECUTE IMMEDIATE lv_dynamic_sql;

	lv_dynamic_sql	:= 'INSERT INTO ' || i_ftbl_name || CHR(10) || i_select_qry;
	EXECUTE IMMEDIATE lv_dynamic_sql;

	RESET client_min_messages;
EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION 'Error (%) occurred during the FOREIGN TABLE creation in the mig_fn_ntz_external_table procedure', SQLERRM;
		RESET client_min_messages;
END;
/

