Here's my attempt, as discussed earlier today. As always, comments welcome. I did provide (and use) a fallback mechanism after all, for the case of a function with a non-empty probin. A few examples from a dump of the regression db:
--
-- Name: tg_hub_adjustslots(character, integer, integer); Type: FUNCTION; Schema
: public; Owner: andrew
--
CREATE FUNCTION tg_hub_adjustslots(character, integer, integer) RETURNS integer
AS $_$
declare
hname alias for $1;
oldnslots alias for $2;
newnslots alias for $3;
begin
if newnslots = oldnslots then
return 0;
end if;
if newnslots < oldnslots then
delete from HSlot where hubname = hname and slotno > newnslots;
return 0;
end if;
for i in oldnslots + 1 .. newnslots loop
insert into HSlot (slotname, hubname, slotno, slotlink)
values ('HS.dummy', hname, i, '');
end loop;
return 0;
end;
$_$
LANGUAGE plpgsql;
-- -- Name: ttdummy(); Type: FUNCTION; Schema: public; Owner: andrew --
CREATE FUNCTION ttdummy() RETURNS "trigger"
AS '/home/andrew/pgwork/tip/pgsql/src/test/regress/regress.so', 'ttdummy'
LANGUAGE c;
-- -- Name: user_relns(); Type: FUNCTION; Schema: public; Owner: andrew --
CREATE FUNCTION user_relns() RETURNS SETOF name
AS $$select relname
from pg_class c, pg_namespace n
where relnamespace = n.oid and
(nspname !~ 'pg_.*' and nspname <> 'information_schema') and
relkind <> 'i' $$
LANGUAGE sql;cheers
andrew
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.68
diff -c -r1.68 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 1 Dec 2003 22:07:58 -0000 1.68
--- doc/src/sgml/ref/pg_dump.sgml 23 Mar 2004 19:30:59 -0000
***************
*** 461,466 ****
--- 461,477 ----
</varlistentry>
<varlistentry>
+ <term><option>-X disable-dollar-quoting</></term>
+ <term><option>--disable-dollar-quoting</></term>
+ <listitem>
+ <para>
+ This option disables the use of dollar quoting for function bodies,
+ and forces them to be quoted using SQL standard strings.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-Z <replaceable
class="parameter">0..9</replaceable></option></term>
<term><option>--compress=<replaceable
class="parameter">0..9</replaceable></option></term>
<listitem>
Index: src/bin/pg_dump/dumputils.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/dumputils.c,v
retrieving revision 1.11
diff -c -r1.11 dumputils.c
*** src/bin/pg_dump/dumputils.c 7 Jan 2004 00:44:21 -0000 1.11
--- src/bin/pg_dump/dumputils.c 23 Mar 2004 19:30:59 -0000
***************
*** 143,148 ****
--- 143,205 ----
/*
+ * Convert a string value to a dollar quoted literal and append it to
+ * the given buffer. If the dqprefix parameter is not NULL then the
+ * dollar quote delimiter will begin with that (after the opening $).
+ *
+ * No escaping is done at all on str, in compliance with the rules
+ * for parsing dollar quoted strings.
+ */
+ void
+ appendStringLiteralDQ(PQExpBuffer buf, const char *str, const char *dqprefix)
+ {
+ static char suffixes[] = "_XXXXXXXX";
+ int nextchar = 0;
+ PQExpBuffer delimBuf = createPQExpBuffer();
+
+ /* start with $ + dqprefix if not NULL */
+ appendPQExpBufferChar(delimBuf, '$');
+ if (dqprefix)
+ appendPQExpBuffer(delimBuf, dqprefix);
+
+ /*
+ * make sure we have a delimiter which (without the trailing $)
+ * is not preent in the string being quoted. We don't check with the
+ * trailing $ so that a string ending in $foo is not quoted with
+ * $foo$.
+ */
+ while (strstr(str, delimBuf->data) != NULL)
+ {
+ appendPQExpBufferChar(delimBuf, suffixes[nextchar++]);
+ nextchar %= sizeof(suffixes)-1;
+ }
+
+ /* add trailing $ */
+ appendPQExpBufferChar(delimBuf, '$');
+
+ /* quote it and we are all done */
+ appendPQExpBuffer(buf,"%s%s%s",delimBuf->data,str,delimBuf->data);
+ destroyPQExpBuffer(delimBuf);
+
+ }
+
+ /*
+ * use dollar quoting if the string to be quoted contains ' or \,
+ * otherwise use standard quoting.
+ */
+
+
+ void appendStringLiteralDQFallback(PQExpBuffer buf, const char *str,
+ bool escapeAll, const char *dqprefix)
+ {
+ if (strchr(str,'\'') == NULL && strchr(str,'\\') == NULL)
+ appendStringLiteral(buf,str,escapeAll);
+ else
+ appendStringLiteralDQ(buf,str,dqprefix);
+ }
+
+
+ /*
* Convert backend's version string into a number.
*/
int
Index: src/bin/pg_dump/dumputils.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/dumputils.h,v
retrieving revision 1.10
diff -c -r1.10 dumputils.h
*** src/bin/pg_dump/dumputils.h 7 Jan 2004 00:44:21 -0000 1.10
--- src/bin/pg_dump/dumputils.h 23 Mar 2004 19:30:59 -0000
***************
*** 21,26 ****
--- 21,30 ----
extern const char *fmtId(const char *identifier);
extern void appendStringLiteral(PQExpBuffer buf, const char *str,
bool escapeAll);
+ extern void appendStringLiteralDQ(PQExpBuffer buf, const char *str,
+ const char *dqprefix);
+ extern void appendStringLiteralDQFallback(PQExpBuffer buf, const char *str,
+ bool escapeAll, const char *dqprefix);
extern int parse_version(const char *versionString);
extern bool parsePGArray(const char *atext, char ***itemarray, int *nitems);
extern bool buildACLCommands(const char *name, const char *type,
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.368
diff -c -r1.368 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 20 Mar 2004 20:09:45 -0000 1.368
--- src/bin/pg_dump/pg_dump.c 23 Mar 2004 19:31:02 -0000
***************
*** 107,112 ****
--- 107,115 ----
static NamespaceInfo *g_namespaces;
static int g_numNamespaces;
+ /* flag to turn on/off dollar quoting */
+ static int disable_dollar_quoting = 0;
+
static void help(const char *progname);
static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
***************
*** 233,238 ****
--- 236,242 ----
*/
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
+ {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
{NULL, 0, NULL, 0}
};
***************
*** 389,394 ****
--- 393,400 ----
/* no-op, still allowed for compatibility */ ;
else if (strcmp(optarg, "disable-triggers") == 0)
disable_triggers = 1;
+ else if (strcmp(optarg,"disable-dollar-quoting") == 0)
+ disable_dollar_quoting = true;
else
{
fprintf(stderr,
***************
*** 681,686 ****
--- 687,694 ----
printf(_(" -x, --no-privileges do not dump privileges
(grant/revoke)\n"));
printf(_(" -X disable-triggers, --disable-triggers\n"
" disable triggers during data-only
restore\n"));
+ printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n"
+ " disable dollar quoting, use SQL
standard quoting\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket
directory\n"));
***************
*** 5076,5082 ****
if (strcmp(prosrc, "-") != 0)
{
appendPQExpBuffer(asPart, ", ");
! appendStringLiteral(asPart, prosrc, false);
}
}
else
--- 5084,5097 ----
if (strcmp(prosrc, "-") != 0)
{
appendPQExpBuffer(asPart, ", ");
! /*
! * where we have bin, use dollar quoting if allowed
! * conditionally on src
! */
! if (disable_dollar_quoting)
! appendStringLiteral(asPart, prosrc, false);
! else
! appendStringLiteralDQFallback(asPart, prosrc, false,
NULL);
}
}
else
***************
*** 5084,5090 ****
if (strcmp(prosrc, "-") != 0)
{
appendPQExpBuffer(asPart, "AS ");
! appendStringLiteral(asPart, prosrc, false);
}
}
--- 5099,5109 ----
if (strcmp(prosrc, "-") != 0)
{
appendPQExpBuffer(asPart, "AS ");
! /* with no bin, dollar quote src unconditionally if allowed */
! if (disable_dollar_quoting)
! appendStringLiteral(asPart, prosrc, false);
! else
! appendStringLiteralDQ(asPart, prosrc, NULL);
}
}
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
