-- -------------------------------------------------
CREATE OR REPLACE FUNCTION disposable_factory ()
RETURNS text
VOLATILE
LANGUAGE plpgsql
AS
$WTF$
DECLARE
 _fmt text;
 _sql text;
BEGIN

_fmt = $FMT$
	-- "Factory function"
	-- Create a table-returning function for table "_fqn"
	-- with the same columns.
	-- But: restricted to the most recent, upto (and including)  asof_date.
	-- The function name is the table name, with '_asof' appended,
	-- and it is created in the same schema as the table.
	-- The generated function takes one argument _datum
	-- , with the same type as tbl.asof_date
	-- --------------------------------------------------------------------
CREATE OR REPLACE FUNCTION create_asof (_fqn text, asof_date text )
RETURNS text
VOLATILE
SECURITY INVOKER
LANGUAGE plpgsql
AS
$func$
DECLARE sql text;
	basepair text[];
	funcpair text[];
	fnc_name text;
	org_name text;
	allkeys text[];
	keys text[];
BEGIN

basepair := %1$s.split_name (_fqn);

funcpair[1] = basepair[1];
funcpair[2] = concat(basepair[2] , '_asof' );

org_name := %1$s.format_pair(basepair);
fnc_name := %1$s.format_pair(funcpair);

allkeys := %1$s.fetch_pk_array (basepair[1] , basepair[2] , asof_date );
keys := array_remove (allkeys, asof_date);

-- RAISE NOTICE 'Allkeys=%%' , all_keys[1];
-- RAISE NOTICE 'Keys=%%' , keys[1];

-- CREATE OR REPLACE FUNCTION %%1$s (IN _datum DATE DEFAULT now() ) -- name
sql := format ('
CREATE OR REPLACE FUNCTION %%1$s (IN _datum %%2$s DEFAULT now() ) -- fnc_name dtype
RETURNS SETOF %%3$s	-- orgtable
STABLE SECURITY INVOKER ROWS 300000
LANGUAGE sql
AS
$omg$
SELECT *		-- all columns
FROM %%3$s src 		-- org table
WHERE %%4$s		-- date treshold
AND NOT EXISTS (
	SELECT * 
	FROM %%3$s nx	-- org table
	WHERE %%5$s	-- key fields
	AND %%6$s	-- date treshold
	AND %%7$s	-- gap
	)
	;
$omg$ 
	;'
	, fnc_name 						-- 1 Function name
	, %1$s.fetch_typename(basepair[1], basepair[2], asof_date)		-- 2 typeof Date field argument
	, org_name 						-- 3 table name
	, %1$s.format_reference ('src', asof_date) || ' <= $1'	-- 4 source Date treshold
	, %1$s.format_equal_and_list ('nx', 'src', keys)		-- 5 Same Keys
	, %1$s.format_reference ('nx', asof_date) || ' <= $1'	-- 6 nx Date treshold
	, %1$s.format_reference ('nx', asof_date) || ' > ' || %1$s.format_reference( 'src', asof_date)	-- 7 Gap Date
	);

-- RAISE NOTICE 'Pair= [%%,%%]' , basepair[1], basepair[2];
-- RAISE NOTICE 'Fnc=%%' , fnc_name;
-- RAISE NOTICE 'Sql=%%' , sql;
EXECUTE sql;
-- RETURN sql ;
RETURN fnc_name ;
END;
$func$;
$FMT$ ;

-- RAISE NOTICE '_Fmt=%' , _fmt;
_sql = format (_fmt, quote_ident(current_schema) );
-- RAISE NOTICE '_Sql=%', _sql;

EXECUTE _sql;
DROP FUNCTION disposable_factory (); -- suicide
-- return _sql;
return 'create_asof';
END;
$WTF$ ;

\echo SELECT disposable_factory();
SELECT disposable_factory();
-- \df create_asof

-- EOF