CREATE OR REPLACE FUNCTION schema_name.generate_audit_trigger(ps_table_name character varying)
  RETURNS void AS
$BODY1$
/**
 *	This function is to generate audit function and trigger for the designated table
 *  under PostgreSQL.
 *	ps_table_name - the source table name
 *	Generated audit function and associated trigger is built on source table
 *	The mapping audit table name should be: ps_table_name || '_audit'
 *	The audit table should be created before running this function. The audit table 
 *  consists of following attributes:
 *	CREATE TABLE schema_name.sample_audit
 *	(
 *	  audit_id bigint NOT NULL DEFAULT nextval('schema_name.sample_audit_id_seq'::regclass),
 *	  id bigint NOT NULL,
 *	  source table columns ......
 *	  action_type character varying(1), 'I', 'D', 'U'
 *	  modifiedby character varying(30),
 *	  modifiedon timestamp without time zone,
 *	  modifiedusing character varying(30),
 *	  CONSTRAINT sample_audit_id PRIMARY KEY (audit_id)
 *	)
 */
DECLARE
	ls_col character varying;
	ls_function_script character varying;
	ls_trigger_script character varying;
	ls_table_schema character varying;
	ls_audit_table_name character varying := '';
	ls_column_string character varying := '';
	ls_old_column_string character varying := '';
	ls_new_column_string character varying := '';
	ls_update_column_string character varying := '';
	ls_user character varying := '';

	cur_columns CURSOR (p_audit_table_name character varying) IS
		(select column_name from information_schema.columns
		where table_name = p_audit_table_name
		and ordinal_position > 1
		order by ordinal_position);
BEGIN
	ls_audit_table_name := ps_table_name || '_audit';

	--RAISE NOTICE '%', ls_audit_table_name;

	OPEN cur_columns(ls_audit_table_name);

	FETCH cur_columns INTO ls_col;

	WHILE FOUND
	LOOP
		--RAISE NOTICE 'inside loop %', ls_col;
		ls_column_string := ls_column_string || ls_col || ', ';
		ls_old_column_string := ls_old_column_string || 'old.'|| ls_col || ', ';
		ls_new_column_string := ls_new_column_string || 'new.'|| ls_col || ', ';
		FETCH cur_columns INTO ls_col;
	END LOOP;

	CLOSE cur_columns;
	
	--RAISE NOTICE 'colstring: %, old_col_string: %, new_col_string: %', ls_column_string, ls_old_column_string, ls_new_column_string;

	select current_user into ls_user;
	
	ls_column_string := substr(ls_column_string, 1, length(ls_column_string) - 2);
	ls_old_column_string := substr(ls_old_column_string, 1, length(ls_old_column_string) - 2);
	ls_old_column_string := replace(ls_old_column_string, 'old.action_type', '''D''');
	ls_old_column_string := replace(ls_old_column_string, 'old.modifiedby', ''''||ls_user||'''');
	ls_old_column_string := replace(ls_old_column_string, 'old.modifiedon', 'current_timestamp');
	ls_old_column_string := replace(ls_old_column_string, 'old.modifiedusing', '''tf_'||ls_audit_table_name||'''');
	ls_new_column_string := substr(ls_new_column_string, 1, length(ls_new_column_string) - 2);
	ls_new_column_string := replace(ls_new_column_string, 'new.modifiedby', ''''||ls_user||'''');
	ls_new_column_string := replace(ls_new_column_string, 'new.modifiedon', 'current_timestamp');
	ls_new_column_string := replace(ls_new_column_string, 'new.modifiedusing', '''tf_'||ls_audit_table_name||'''');
	ls_update_column_string := ls_new_column_string;
	ls_new_column_string := replace(ls_new_column_string, 'new.action_type', '''I''');
	ls_update_column_string := replace(ls_update_column_string, 'new.action_type', '''U''');

	select table_schema into ls_table_schema from information_schema.tables where table_name = ls_audit_table_name;

	--RAISE NOTICE 'ls_table_schema:%', ls_table_schema;
	
	ls_function_script := 'CREATE OR REPLACE FUNCTION ' || ls_table_schema || '.tf_' || ls_audit_table_name || '() RETURNS trigger AS $BODY$ 
	begin
		if tg_op = ''DELETE'' then 
			INSERT INTO '|| ls_table_schema || '.' || ls_audit_table_name || '(' || ls_column_string || ') 
			SELECT ' || ls_old_column_string || ';
			RETURN old;
		elseif tg_op = ''INSERT'' then 
			INSERT INTO '|| ls_table_schema || '.' || ls_audit_table_name || '(' || ls_column_string || ') 
			SELECT ' || ls_new_column_string || ';
			RETURN new;
		elseif tg_op = ''UPDATE'' then
			INSERT INTO '|| ls_table_schema || '.' || ls_audit_table_name || '(' || ls_column_string || ') 
			SELECT ' || ls_update_column_string || ';
			RETURN new;
		end if;
	end; $BODY$
	LANGUAGE plpgsql VOLATILE
	COST 100;';

	--RAISE NOTICE 'ls_function_script: %', ls_function_script;
	EXECUTE ls_function_script;

	ls_trigger_script := 'CREATE TRIGGER trigger_' || ps_table_name || ' AFTER INSERT OR UPDATE OR DELETE ON ' || 
				ls_table_schema || '.' || ps_table_name || ' FOR EACH ROW 
				EXECUTE PROCEDURE ' || ls_table_schema || '.tf_' || ls_audit_table_name || '();';
	--RAISE NOTICE 'ls_trigger_script: %', ls_trigger_script;
	EXECUTE ls_trigger_script;
END;
$BODY1$
  LANGUAGE plpgsql VOLATILE
  COST 100;