Hi there!
I followed the discussion about the schema resolution, and I really
think there is need for an early bound (at function definition time)
version of CURRENT_SCHEMA (the first member of search_path)
Avoiding hard-coding of schema names, (and avoiding polluting the actual
users schema) is hard.
My current code generates some plpgsql functions, which need some helper
functions to construct fcolumn lists, query fragments, etc.
These helpers should live in the same schema, IMHO
It is not impossible: I ended up with the following kludge to refer to
functions in the same schema as the calling function.
It involves an extra layer of dynamic SQL, which self-destructs after
use. It is not pretty, but it works.
Example of such a nested dynamic function is attached. (the helper
functions are not included, but they live in the same "compilation unit")
Cheers,
Adriaan van Kessel
-- -------------------------------------------------
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
