thank you thank you thank you thank you thank you. I can feel my hair
growing back already.
<josh(at)agliodbs To: "Patrick Hatcher" <PHatcher(at)macys(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Escaping ' in a function
> 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
> 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 '
> tblname ALIAS FOR $1;
> old_view RECORD;
> v_sql varchar;
> /* 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
FOR old_view IN SELECT * FROM pg_views WHERE definition ~* tblname LOOP
No quotes are necessary with this version
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.
Aglio Database Solutions
pgsql-novice by date
|Next:||From: dr_sad(surguttel)||Date: 2002-08-28 02:30:13|
|Subject: PHP & Postgres|
|Previous:||From: Josh Berkus||Date: 2002-08-27 23:13:58|
|Subject: Re: Escaping ' in a function|