Skip site navigation (1) Skip section navigation (2)

Re: Escaping ' in a function

From: "Patrick Hatcher" <PHatcher(at)macys(dot)com>
To: josh(at)agliodbs(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Escaping ' in a function
Date: 2002-08-27 23:30:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
thank you thank you thank you thank you thank you.  I can feel my hair
growing back already.

Patrick Hatcher

                    Josh Berkus                                                                                                               
                    <josh(at)agliodbs       To:     "Patrick Hatcher" <PHatcher(at)macys(dot)com>, pgsql-novice(at)postgresql(dot)org                          
                    .com>                cc:                                                                                                  
                                         Subject:     Re: [NOVICE] Escaping ' in a function                                                   
                    04:13 PM                                                                                                                  
                    Please respond                                                                                                            
                    to josh                                                                                                                   


> 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;
> 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

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.

-Josh Berkus
 Aglio Database Solutions
 San Francisco

pgsql-novice by date

Next:From: dr_sad(surguttel)Date: 2002-08-28 02:30:13
Subject: PHP & Postgres
Previous:From: Josh BerkusDate: 2002-08-27 23:13:58
Subject: Re: Escaping ' in a function

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group