hey all!
I want to use a "LIKE" comparison in my plpgsql script, but I'm unsure how to
do it... What I have right now doesn't work as hoped (although I did expect it
to not work).
UPDATE dir SET udate = curtime WHERE ''bind_dir.dir''::text LIKE ''%mydir%''::text AND
dir.uid = bind_dir.uid;
The above comparison fails as Postgres interprets the variables bind_dir.dir
and mydir as text when enclosed in two single quotes... but I cannot just tag
the '%' percent sign along after 'mydir', or the function won't compile. So
how can I utilize the LIKE (or ~~) operator in my plpgsql scripts?
I can provide futher information (table dumps, scope of what the heck I'm
trying to do, ect. upon request, but this is probably just a silly oversight on
my part.)
If you could reply to me directly, I'd appreciate it as I'm not on the list
right now... Thanks!
-Full function-
create function bind_clean_bind () RETURNS OPAQUE AS '
DECLARE
bind_dir RECORD;
mydir dir.dir%TYPE;
curtime datetime;
BEGIN
IF TG_OP = ''UPDATE'' OR TG_OP = ''DELETE'' THEN
SELECT dir INTO mydir FROM dir WHERE uid = OLD.duid;
IF FOUND THEN
FOR bind_dir IN SELECT uid,dir FROM dir;
curtime := ''now'';
UPDATE dir SET udate = curtime WHERE ''bind_dir.dir''::text LIKE ''%mydir%''::text AND dir.uid = bind_dir.uid;
END LOOP;
END IF;
END IF;
IF TG_OP = ''UPDATE'' OR TG_OP = ''INSERT'' THEN
SELECT dir INTO mydir FROM dir WHERE uid = NEW.duid;
IF FOUND THEN
FOR bind_dir IN SELECT uid,dir FROM dir;
curtime := ''now'';
UPDATE dir SET udate = curtime WHERE ''bind_dir.dir''::text LIKE ''%mydir%''::text AND dir.uid = bind_dir.uid;
END LOOP;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
--
Sincerely,
Jason Boxman
http://edseek.com/ - Your source for educational excellence
"Two roads diverged in a wood, and I-- \ I took the one lessed
traveled by, \ And that has made all the difference."
-Robert Frost, "The Road Not Taken"