Folks,

Given the amount of qoute nesting we do in Postgres, I thought that we need a 
function that handles automatic doubling of quotes within strings.   I've 
written one in PL/pgSQL (below).  I'd really love to see this turned into a 
builtin C function.

-Josh

CREATE FUNCTION double_quote(text) returns text as '
DECLARE bad_string ALIAS for $1;
        good_string text;
        current_pos INT;
        old_pos INT;
BEGIN
        IF bad_string IS NULL or bad_string = '''' THEN
                RETURN bad_string;
        END IF;
        good_string := bad_string;
        current_pos :=  STRPOS(good_string, chr(39));
        WHILE current_pos > 0 LOOP
                old_pos := current_pos;
                good_string := SUBSTR(good_string, 1, (current_pos - 1)) ||
                        repeat(chr(39), 2) || SUBSTR(good_string, (current_pos 
+ 1));
                current_pos := STRPOS(SUBSTR(good_string, (old_pos + 2)), 
chr(39));
                IF current_pos > 0 THEN
                        current_pos := current_pos + old_pos + 1;
                END IF;
        END LOOP;
RETURN good_string;
END;'
LANGUAGE 'plpgsql'
WITH (ISCACHABLE, ISSTRICT);



Mon, 17 Jun 2002 13:34:01 -0700
Josh Berkus <josh@agliodbs.com>
Request for builtin function: Double_quote
Josh, 
I'm not sure what you mean by 'builtin C function'. 
There is one already 
 size_t PQescapeString (char *to, const char *from, size_t length); 
Or do you mean a String Function like 
 substring(string [from integer] [for integer]) 
I would rather call it 'builtin sql function'. 

Regards, Christoph 

> 
> Folks,
> 
> Given the amount of qoute nesting we do in Postgres, I thought that we need a
> function that handles automatic doubling of quotes within strings.   I've 
> written one in PL/pgSQL (below).  I'd really love to see this turned into a 
> builtin C function.
> 
> -Josh
> 

Tue, 18 Jun 2002 9:35:34 METDST
Christoph Haller <ch@rodos.fzk.de>
Re: Request for builtin function: Double_quote
Josh Berkus <josh@agliodbs.com> writes:
> Given the amount of qoute nesting we do in Postgres, I thought that we need a 
> function that handles automatic doubling of quotes within strings.   I've 
> written one in PL/pgSQL (below).  I'd really love to see this turned into a 
> builtin C function.

What does this do that isn't already done by quote_literal?

			regards, tom lane

Tue, 18 Jun 2002 10:48:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Request for builtin function: Double_quote 
Chris, Tom:

Yes, thank you Chris, I meant a builtin SQL function.

> > Given the amount of qoute nesting we do in Postgres, I thought that
> we need a 
> > function that handles automatic doubling of quotes within strings.
>   I've 
> > written one in PL/pgSQL (below).  I'd really love to see this
> turned into a 
> > builtin C function.
> 
> What does this do that isn't already done by quote_literal?

Well, first off, quote_literal isn't in the documentation under
"Functions and Operators".    So this is the first I've heard about it
-- or probably anyone else outside the core team.   How long has it
been around?

Second, double_quote does not return the outside quotes, just the
inside ones ... it's for passing string values to EXECUTE statements.
  However, now that I know that quote_literal exists, I can simplify
the double_quote statement considerably.  

Therefore, I withdraw my initial request, and request instead that
quote_literal be added to the function documentation in String
Functions and Operators.

I will event supply text for the functions table:

function			returns		
quote_literal(string text)	text		

explain
Returns the entire string passed to it, including quote marks.  Useful
for nesting quotes, such as in the EXECUTEing dynamic queries.

example			result
quote_literal('O''Reilly')	'O''Reilly'


-Josh Berkus

-Josh Berkus

Tue, 18 Jun 2002 08:49:33 -0700
"Josh Berkus" <josh@agliodbs.com>
Re: Request for builtin function: Double_quote 
"Josh Berkus" <josh@agliodbs.com> writes:
> Well, first off, quote_literal isn't in the documentation under
> "Functions and Operators".    So this is the first I've heard about it
> -- or probably anyone else outside the core team.   How long has it
> been around?

Awhile; however, the only documentation was in the discussion of EXECUTE
in the pl/pgsql chapter of the Programmer's Guide, which is probably not
the best place.

> Therefore, I withdraw my initial request, and request instead that
> quote_literal be added to the function documentation in String
> Functions and Operators.

Done; I also added its sister function quote_ident.  See the devel
docs at
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-string.html

			regards, tom lane



Mon, 24 Jun 2002 18:40:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Request for builtin function: Double_quote 

Tom,

> Done; I also added its sister function quote_ident.  See the devel
> docs at
> http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-string.html

Tante Grazie.

-- 
-Josh Berkus




Mon, 24 Jun 2002 20:32:44 -0700
Josh Berkus <josh@agliodbs.com>
Re: Request for builtin function: Double_quote