Foolish question about <<<< SELECT INTO rec xxx, xxxx, xxx, xxx WHERE YYYYYY ilike %$2 >>>>

From: Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr>
To: pgadmin-hackers(at)greatbridge(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Foolish question about <<<< SELECT INTO rec xxx, xxxx, xxx, xxx WHERE YYYYYY ilike %$2 >>>>
Date: 2001-04-17 20:11:31
Message-ID: 4.2.0.58.20010417212507.00a487d0@pop.freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-04-17 20:41:35 Re: Foolish question about <<<< SELECT INTO rec xxx, xxxx, xxx, xxx WHERE YYYYYY ilike %$2 >>>>
Previous Message Trond Eivind =?iso-8859-1?q?Glomsr=F8d?= 2001-04-17 19:55:58 Re: 7.1 on 7.1