Request for builtin function: Double_quote
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);
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
Show quoted text
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
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
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
"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
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