Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })
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 30
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
Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })
On 27-09-2023 04:03, Erik Wienhold wrote: ccing list On 2023-09-27 00:12 +0200, dld write: On 26-09-2023 23:47, Erik Wienhold wrote: On 2023-09-26 14:44 +0200, dld wrote: 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) The helper functions can't be created in a common schema with a fixed name? Yes, they could. But I try to avoid hard coding the name all over the place. Ah, I see. But still wondering if this is necessary. I do not want to interfere, I do not want to pollute their schema with my nonsense-functions.. Again, why can't create_asof() and the helper/worker functions be in a hard coded schema? Are those functions defined once in the database or does each user get their own version, perhaps in a multitenancy design? And who is calling create_asof()? Yes they could. my_separate_schema.create_asof() is intended to be called by the "end user" of the "package" From whatever current_schema or search_path [s]he happens to be in. I'm currently working on a database that I also designed in large parts where trigger functions (SECURITY DEFINER) create views that give users a restricted view of the data for ease of use. Quite similar to that create_asof() function but with hard coded schema names. So I'm also interested to learn what designs other people came up with. Me too. And: I would really like another version of current_schema() that is resolved/bound at the moment the function is defined. I just want to keep them in my own secret schema. [remember POSTGIS?] Secretive for having security through obscurity? But you can't really hide schema information when users still need access to system catalogs. But you can decide to revoke EXECUTE privilege from those functions and give users a few SECURITY DEFINER functions as entry points to the "private" parts of the schema. It is not about security. security is orthogonal to this. And the SECURITY DEFINER is already present. The factory function will generate a function in the end-users schema , but only if this end-user has sufficient rights. Anything special about PostGIS in this regard? In my databases PostGIS either lives in public or a dedicated schema. But there's nothing secretive about it. IIRC postgis needs to be in the search_path, or it will suffer the same restrictions. HTH, AvK
Re: Indexing fragments of a column's value ?
You can index on expressions, and these will be recognised by the query generator. drop schema tmp CASCADE; create schema tmp; set search_path = tmp; CREATE TABLE bagger ( eight CHAR(8) NOT NULL PRIMARY KEY , more text ); CREATE INDEX bagger_idx_12 ON bagger (substr(eight,1,2)); CREATE INDEX bagger_idx_34 ON bagger (substr(eight,3,2)); CREATE INDEX bagger_idx_58 ON bagger (substr(eight,5,4)); INSERT INTO bagger(eight, more) SELECT translate(to_hex( gs), ' ' , '0') , gs::text FROM generate_series(0,40, 64999) gs ; VACUUM ANALYZE bagger; EXPLAIN ANALYZE SELECT * FROM bagger WHERE 1=1 -- AND eight >= '00' AND eight < '05' AND substr(eight, 1,2) >= '30' AND substr(eight,1,2) < '05' AND substr(eight, 3,2) >= '90' AND substr(eight,3,2) < 'A5' AND substr(eight, 5,4) >= '' AND substr(eight,5,4) < '' ; /*** The optimiser is smart enough to ignore one of the indexes. */ QUERY PLAN - Bitmap Heap Scan on bagger (cost=41.46..59.62 rows=1 width=19) (actual time=0.049..0.056 rows=0 loops=1) Recheck Cond: ((substr((eight)::text, 1, 2) >= '30'::text) AND (substr((eight)::text, 1, 2) < '05'::text) AND (substr((eight)::text, 3, 2) >= '90'::text) AND (substr((eight)::text, 3, 2) < 'A5'::text)) Filter: ((substr((eight)::text, 5, 4) >= ''::text) AND (substr((eight)::text, 5, 4) < ''::text)) -> BitmapAnd (cost=41.46..41.46 rows=16 width=0) (actual time=0.042..0.047 rows=0 loops=1) -> Bitmap Index Scan on bagger_idx_12 (cost=0.00..4.47 rows=308 width=0) (actual time=0.039..0.039 rows=0 loops=1) Index Cond: ((substr((eight)::text, 1, 2) >= '30'::text) AND (substr((eight)::text, 1, 2) < '05'::text)) -> Bitmap Index Scan on bagger_idx_34 (cost=0.00..36.74 rows=3205 width=0) (never executed) Index Cond: ((substr((eight)::text, 3, 2) >= '90'::text) AND (substr((eight)::text, 3, 2) < 'A5'::text)) Planning Time: 5.487 ms Execution Time: 0.310 ms (10 rows) HTH, AvK
