From: | "Patrick Hatcher" <PHatcher(at)macys(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Escaping ' in a function |
Date: | 2002-08-27 22:52:32 |
Message-ID: | OF7B5CB2EC.AB310DDE-ON88256C22.0077FD8B-88256C22.007D9FC2@fds.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
If I had hair, I would have lost it by now.
I'm trying to create a function that passes a string that will need to be
quoted. I finally figured out how many single quotes I needed and as a
test, I've shown it in v_sql. However, I now need to do the same thing for
my FOR...LOOP query.
The end result should be something like this:
Select * from pg_views where definition ~* 'product_non_master_v'
I've tried numerous variations of quotes, but my return value is always
null. If I output the v_sql variable, it gives me what I need to run a
successful query.
Any help would be greatly appreciated:
CREATE or REPLACE FUNCTION recompileview(varchar) RETURNS varchar AS '
DECLARE
tblname ALIAS FOR $1;
old_view RECORD;
v_sql varchar;
begin
/* This is what I need */
v_sql := ''Select * from pg_views where definition ~* '''''' || tblname ||
'''''''' ;
FOR old_view in Select * from pg_views where definition ~*
''''tblname'''''' LOOP
v_name := v_name || old_view.viewname;
END LOOP;
RETURN v_name;
end;
' LANGUAGE 'plpgsql';
TIA
Patrick Hatcher
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2002-08-27 23:13:58 | Re: Escaping ' in a function |
Previous Message | Josh Berkus | 2002-08-27 17:05:56 | Re: Find out more |