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