Escaping ' in a function

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

Responses

Browse pgsql-novice by date

  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