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_source 
ilike 
''''%'' || $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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to