Обсуждение: Escaping ' in a function
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
Patrick, > 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. Ah, the classic qouted-quoted-quoted string problem. Here's what I sometimes do for these procedures: 1. Build the procedure, without doubling any quotes. 2. Use search-and-replace on just the string value to double those quotes. 3. Use seach-and-replace on the whole procedure to double all quotes. I find that this is more likely to yield me the correct number of quotes. > 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 Use: FOR old_view IN SELECT * FROM pg_views WHERE definition ~* tblname LOOP No quotes are necessary with this version Or: FOR old_view IN EXECUTE v_sql LOOP BTW, this procedure is going to cause havoc if you have views referencing other views. They won't necessarily be re-created in order. -- -Josh Berkus Aglio Database Solutions San Francisco
thank you thank you thank you thank you thank you. I can feel my hair growing back already. Patrick Hatcher Macys.Com Josh Berkus <josh@agliodbs To: "Patrick Hatcher" <PHatcher@macys.com>, pgsql-novice@postgresql.org .com> cc: Subject: Re: [NOVICE] Escaping ' in a function 08/27/2002 04:13 PM Please respond to josh Patrick, > 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. Ah, the classic qouted-quoted-quoted string problem. Here's what I sometimes do for these procedures: 1. Build the procedure, without doubling any quotes. 2. Use search-and-replace on just the string value to double those quotes. 3. Use seach-and-replace on the whole procedure to double all quotes. I find that this is more likely to yield me the correct number of quotes. > 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 Use: FOR old_view IN SELECT * FROM pg_views WHERE definition ~* tblname LOOP No quotes are necessary with this version Or: FOR old_view IN EXECUTE v_sql LOOP BTW, this procedure is going to cause havoc if you have views referencing other views. They won't necessarily be re-created in order. -- -Josh Berkus Aglio Database Solutions San Francisco