Hello Dave & friends,
I am working on the PgAdmin query loader project writing as much possible
code server-side in PL/pgSQL.
For the purpose of function 'compilation' (let's call it like that), I
create two temporary tables: compiler_function which holds the list of
PL/PgSQL functions to compile,
and compiler_dependency which holds the list of dependencies. After
compilation of functions, these two tables are dropped.
To find function dependencies, I need to run this (problematic) query on
each function:
CREATE FUNCTION pgadmin_comp_dependency_init (int4, text)
RETURNS int4
AS 'DECLARE /* $1 holds the function iod, $2 holds the function name.*/
rec record; v_query1 varchar; v_query2 varchar; BEGIN SELECT INTO rec
compiler_function.function_oid FROM compiler_function WHERE function_source ilike %$2%; /*
<----- $2
holds the name of the function on which is performed a dependency search. */ IF FOUND THEN
/* < --- The rest is OK : EXECUTE works perfectly when there is no issue
in testing results*/ v_query2 := ''INSERT INTO compiler_dependency (dependency_from,
dependency_to ) SELECT compiler_function.function_oid, '' || text($1) || '' FROM compiler_function WHERE
function_sourceilike
''''%'' || $2 || ''%'''';''; execute (v_query2); RETURN 1; ELSE RETURN 0; END
IF;END;
'
LANGUAGE 'plpgsql' ;
My problem is that "ilike %$2%;" (line 13) does not work.
PL/PgSQL thinks % is the type of $2.
I tried the EXECUTE variable alternative without results.
Any idea to run the 'SELECT INTO rec xxx, xxxx, xxx, xxx WHERE YYYYYY ilike
%$2%' ?
Is there a workaround like using a server-side function similar to
ilike(varchar, varchar)->boolean ?
Greeting from Jean-Michel POURE, Paris
