Обсуждение: 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