pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Request for builtin function: Double_quote
6+ messages / 3 participants
[nested] [flat]
* Request for builtin function: Double_quote
@ 2002-06-17 20:34 Josh Berkus <josh@agliodbs.com>
1970-01-01 00:00 ` Re: Request for builtin function: Double_quote Christoph Haller <ch@rodos.fzk.de>
2002-06-18 14:48 ` Re: Request for builtin function: Double_quote Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Josh Berkus @ 2002-06-17 20:34 UTC (permalink / raw)
To: pgsql-sql; pgsql-hackers@postgresql.org
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);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Request for builtin function: Double_quote
2002-06-17 20:34 Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
@ 1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
1 sibling, 0 replies; 6+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Request for builtin function: Double_quote
2002-06-17 20:34 Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
@ 2002-06-18 14:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-18 15:49 ` Re: Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-06-18 14:48 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Request for builtin function: Double_quote
2002-06-17 20:34 Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
2002-06-18 14:48 ` Re: Request for builtin function: Double_quote Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-06-18 15:49 ` Josh Berkus <josh@agliodbs.com>
2002-06-24 22:40 ` Re: Request for builtin function: Double_quote Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2002-06-18 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; josh@agliodbs.com; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Request for builtin function: Double_quote
2002-06-17 20:34 Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
2002-06-18 14:48 ` Re: Request for builtin function: Double_quote Tom Lane <tgl@sss.pgh.pa.us>
2002-06-18 15:49 ` Re: Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
@ 2002-06-24 22:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-25 03:32 ` Re: Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-06-24 22:40 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Request for builtin function: Double_quote
2002-06-17 20:34 Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
2002-06-18 14:48 ` Re: Request for builtin function: Double_quote Tom Lane <tgl@sss.pgh.pa.us>
2002-06-18 15:49 ` Re: Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
2002-06-24 22:40 ` Re: Request for builtin function: Double_quote Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-06-25 03:32 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2002-06-25 03:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-06-25 03:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-17 20:34 Request for builtin function: Double_quote Josh Berkus <josh@agliodbs.com>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2002-06-18 14:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-18 15:49 ` Josh Berkus <josh@agliodbs.com>
2002-06-24 22:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-25 03:32 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox