Thread: language handlers in public schema?
Is there any reason for us to keep putting the language handler functions in the public schema? If they were put in, say, pg_catalog (as I see Peter's pl/sh language does for its handler) then the public schema could be dropped by people who want to do that with no ill effect, AFAICS. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Is there any reason for us to keep putting the language handler
> functions in the public schema?
I believe one of the issues there is that pg_dump doesn't dump functions
that are in pg_catalog. You could possibly fix it to make an exception
for functions that are referenced by pg_language entries, but I think
this would be a nontrivial change.
regards, tom lane
Tom Lane wrote: >Andrew Dunstan <andrew@dunslane.net> writes: > > >>Is there any reason for us to keep putting the language handler >>functions in the public schema? >> >> > >I believe one of the issues there is that pg_dump doesn't dump functions >that are in pg_catalog. You could possibly fix it to make an exception >for functions that are referenced by pg_language entries, but I think >this would be a nontrivial change. > > Hmm. Could we not just add something this condition: or pg_catalog.format_type(prorettype, NULL) != 'language_handler' in pg_dump.c around line 2191? I'd like to get this done, but I don't think I'll have time before July 1. How about putting it on the TODO list? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Hmm. Could we not just add something this condition:
> or pg_catalog.format_type(prorettype, NULL) != 'language_handler'
> in pg_dump.c around line 2191?
You forgot about the validators, which are not so easily identified.
regards, tom lane
I wrote: > > Hmm. Could we not just add something this condition: > > or pg_catalog.format_type(prorettype, NULL) != 'language_handler' > > in pg_dump.c around line 2191? > > This won't work - it would miss the validators. Slightly more complex than I thought. cheers andrew
On 2005-06-24, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> Is there any reason for us to keep putting the language handler >> functions in the public schema? > > I believe one of the issues there is that pg_dump doesn't dump functions > that are in pg_catalog. You could possibly fix it to make an exception > for functions that are referenced by pg_language entries, but I think > this would be a nontrivial change. I think there's a case for a schema to exist by default that (a) does not have public CREATE rights and (b) is not the default place to create objects. There might even be a case for two of them: one on the default search path and one not. Then stuff like languages, small contrib modules (large ones should create their own schema), etc., can all default to installing somewhere other than public. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew Dunstan <andrew@dunslane.net> writes:
> This won't work - it would miss the validators. Slightly more complex
> than I thought.
Well, there's always the brute-force solution:
regression=# explain analyze select * from pg_proc WHERE NOT proisagg AND (pronamespace != (select oid from
pg_namespacewhere nspname = 'pg_catalog')
regression(# OR oid in (select lanplcallfoid from pg_language) OR oid in (select lanvalidator from pg_language));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------Seq Scan
onpg_proc (cost=3.16..90.79 rows=1754 width=283) (actual time=17.562..22.686 rows=115 loops=1) Filter: ((NOT
proisagg)AND ((pronamespace <> $0) OR (hashed subplan) OR (hashed subplan))) InitPlan -> Seq Scan on pg_namespace
(cost=0.00..1.06rows=1 width=4) (actual time=0.063..0.083 rows=1 loops=1) Filter: (nspname =
'pg_catalog'::name) SubPlan -> Seq Scan on pg_language (cost=0.00..1.04 rows=4 width=4) (actual time=0.011..0.042
rows=4loops=1) -> Seq Scan on pg_language (cost=0.00..1.04 rows=4 width=4) (actual time=0.022..0.057 rows=4
loops=1)Totalruntime: 24.760 ms
(9 rows)
I had thought this would be excessively slow compared to the present
regression=# explain analyze select * from pg_proc WHERE NOT proisagg AND pronamespace != (select oid from pg_namespace
wherenspname = 'pg_catalog'); QUERY PLAN
--------------------------------------------------------------------------------------------------------------Seq Scan
onpg_proc (cost=1.06..78.22 rows=1002 width=283) (actual time=10.537..11.915 rows=112 loops=1) Filter: ((NOT
proisagg)AND (pronamespace <> $0)) InitPlan -> Seq Scan on pg_namespace (cost=0.00..1.06 rows=1 width=4) (actual
time=0.068..0.091rows=1 loops=1) Filter: (nspname = 'pg_catalog'::name)Total runtime: 12.799 ms
(6 rows)
but it doesn't look intolerable at all.
Next question is whether there are any other places that would be
affected besides createlang/droplang. I can't think of any offhand,
but ...
regards, tom lane
Tom Lane wrote: >regression=# explain analyze select * from pg_proc WHERE NOT proisagg AND (pronamespace != (select oid from pg_namespacewhere nspname = 'pg_catalog') >regression(# OR oid in (select lanplcallfoid from pg_language) OR oid in (select lanvalidator from pg_language)); > > > Yeah. I think we'd need to add "where langlcallfoid != 0" so we don't pick up the internal/C/sql handlers. However, on closer inspection it appears that doind all this in pg_dump would be lots more invasive than I first thought. An alternative would be to adopt AndrewSN's suggestion of an extra schema (or possibly two) created by initdb where we put extra stuff. That would mean a very simple addition to initdb.c and no changes to pg_dump, except ... >Next question is whether there are any other places that would be >affected besides createlang/droplang. I can't think of any offhand, >but ... > > > > me either, but I wonder if we should provide an option on pg_dump to restore function handlers found in public to whatever we decide about the above. thoughts? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Yeah. I think we'd need to add "where langlcallfoid != 0" so we don't
> pick up the internal/C/sql handlers. However, on closer inspection it
> appears that doind all this in pg_dump would be lots more invasive than
> I first thought.
Why --- what else is needed beyond the addition of those clauses to the
one query?
> me either, but I wonder if we should provide an option on pg_dump to
> restore function handlers found in public to whatever we decide about
> the above.
I don't see the need. If they were in public before, they can stay
there --- or the DBA can run createlang before running pg_restore to
put them where he wants.
regards, tom lane
Tom Lane wrote: >Andrew Dunstan <andrew@dunslane.net> writes: > > >>Yeah. I think we'd need to add "where langlcallfoid != 0" so we don't >>pick up the internal/C/sql handlers. However, on closer inspection it >>appears that doind all this in pg_dump would be lots more invasive than >>I first thought. >> >> > >Why --- what else is needed beyond the addition of those clauses to the >one query? > > There are tests for both the function and the handler based on finfo->dobj.namespace->dump that inhibit output if we're in the catalog schema. If we go down this path ISTM the simplest thing would be to add a field to the FuncInfo object to allow it to be marked as a a handler/validator. Also, I think pg_dump *never* quotes the handler name or qualifies it with a schema name - that looks like it might be a bug, regardless of this. > > >>me either, but I wonder if we should provide an option on pg_dump to >>restore function handlers found in public to whatever we decide about >>the above. >> >> > >I don't see the need. If they were in public before, they can stay >there --- or the DBA can run createlang before running pg_restore to >put them where he wants. > > > > OK, I'm all for a simple life. cheers andrew
This patch implements putting language handlers for the optional PLs
into pg_catalog rather than public, and supports dumping languages whose
handlers are found there. This will make it easier to drop the public
schema if desired.
Unlike the previous patch, the comments have been updated and I have
reformatted some code to meet Alvarro's request to stick to 80 cols. (I
actually aghree with this - it makes printing the code much nicer).
I think I did the right thing w.r.t versions earlier than 7.3, but I
have no real way of checking, so that should be checked by someone with
more/older knowledge than me ;-)
cheers
andrew
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.410
diff -c -r1.410 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 21 Jun 2005 20:45:44 -0000 1.410
--- src/bin/pg_dump/pg_dump.c 28 Jun 2005 00:22:34 -0000
***************
*** 2146,2151 ****
--- 2146,2152 ----
int i_proargtypes;
int i_prorettype;
int i_proacl;
+ int i_is_pl_handler;
/* Make sure we are in proper schema */
selectSourceSchema("pg_catalog");
***************
*** 2154,2168 ****
if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(query,
"SELECT tableoid, oid, proname, prolang, "
"pronargs, proargtypes, prorettype, proacl, "
"pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename "
"FROM pg_proc "
"WHERE NOT proisagg "
! "AND pronamespace != "
! "(select oid from pg_namespace where nspname = 'pg_catalog')");
}
else if (g_fout->remoteVersion >= 70100)
{
--- 2155,2190 ----
if (g_fout->remoteVersion >= 70300)
{
+ /*
+ * We now collect info on pg_catalog resident functions, but
+ * only if they are language call handlers or validators, and
+ * only for non-default languages (i.e. not internal/C/SQL).
+ */
appendPQExpBuffer(query,
"SELECT tableoid, oid, proname, prolang, "
"pronargs, proargtypes, prorettype, proacl, "
"pronamespace, "
! "(select usename from pg_user "
! " where proowner = usesysid) as usename, "
! "CASE WHEN oid IN "
! " (select lanplcallfoid from pg_language "
! " where lanplcallfoid != 0) THEN true "
! " WHEN oid IN "
! " (select lanvalidator from pg_language "
! " where lanplcallfoid != 0) THEN true "
! " ELSE false END AS is_pl_handler "
"FROM pg_proc "
"WHERE NOT proisagg "
! "AND (pronamespace != "
! " (select oid from pg_namespace "
! " where nspname = 'pg_catalog')"
! " OR oid IN "
! " (select lanplcallfoid from pg_language "
! " where lanplcallfoid != 0) "
! " OR oid IN "
! " (select lanvalidator from pg_language "
! " where lanplcallfoid != 0))"
! );
}
else if (g_fout->remoteVersion >= 70100)
{
***************
*** 2171,2177 ****
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
--- 2193,2201 ----
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user "
! " where proowner = usesysid) as usename, "
! "false AS is_pl_handler "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
***************
*** 2180,2191 ****
{
appendPQExpBuffer(query,
"SELECT "
! "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') AS tableoid, "
"oid, proname, prolang, "
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
--- 2204,2218 ----
{
appendPQExpBuffer(query,
"SELECT "
! "(SELECT oid FROM pg_class "
! " WHERE relname = 'pg_proc') AS tableoid, "
"oid, proname, prolang, "
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user "
! " where proowner = usesysid) as usename, "
! "false AS is_pl_handler "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
***************
*** 2210,2215 ****
--- 2237,2243 ----
i_proargtypes = PQfnumber(res, "proargtypes");
i_prorettype = PQfnumber(res, "prorettype");
i_proacl = PQfnumber(res, "proacl");
+ i_is_pl_handler = PQfnumber(res,"is_pl_handler");
for (i = 0; i < ntups; i++)
{
***************
*** 2218,2230 ****
finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
AssignDumpId(&finfo[i].dobj);
finfo[i].dobj.name = strdup(PQgetvalue(res, i, i_proname));
! finfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
finfo[i].dobj.catId.oid);
finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
if (finfo[i].nargs == 0)
finfo[i].argtypes = NULL;
else
--- 2246,2261 ----
finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
AssignDumpId(&finfo[i].dobj);
finfo[i].dobj.name = strdup(PQgetvalue(res, i, i_proname));
! finfo[i].dobj.namespace =
! findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
finfo[i].dobj.catId.oid);
finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
+ finfo[i].isProlangFunc =
+ strcmp(PQgetvalue(res, i, i_is_pl_handler), "t") == 0;
if (finfo[i].nargs == 0)
finfo[i].argtypes = NULL;
else
***************
*** 2235,2241 ****
}
if (strlen(finfo[i].usename) == 0)
! write_msg(NULL, "WARNING: owner of function \"%s\" appears to be invalid\n",
finfo[i].dobj.name);
}
--- 2266,2273 ----
}
if (strlen(finfo[i].usename) == 0)
! write_msg(NULL,
! "WARNING: owner of function \"%s\" appears to be invalid\n",
finfo[i].dobj.name);
}
***************
*** 4921,4943 ****
return;
/*
! * Current theory is to dump PLs iff their underlying functions will
! * be dumped (are in a dumpable namespace, or have a non-system OID in
! * pre-7.3 databases). Actually, we treat the PL itself as being in
* the underlying function's namespace, though it isn't really. This
* avoids searchpath problems for the HANDLER clause.
*
- * If the underlying function is in the pg_catalog namespace, we won't
- * have loaded it into finfo[] at all; therefore, treat failure to
- * find it in finfo[] as indicating we shouldn't dump it, not as an
- * error condition. Ditto for the validator.
*/
funcInfo = findFuncByOid(plang->lanplcallfoid);
if (funcInfo == NULL)
return;
! if (!funcInfo->dobj.namespace->dump)
return;
if (OidIsValid(plang->lanvalidator))
--- 4953,4971 ----
return;
/*
! * We dump PLs iff their underlying call handler functions have been
! * marked as language functions (or have a non-system OID in
! * pre-7.3 databases). We treat the PL itself as being in
* the underlying function's namespace, though it isn't really. This
* avoids searchpath problems for the HANDLER clause.
*
*/
funcInfo = findFuncByOid(plang->lanplcallfoid);
if (funcInfo == NULL)
return;
! if (!funcInfo->isProlangFunc && !funcInfo->dobj.namespace->dump)
return;
if (OidIsValid(plang->lanvalidator))
***************
*** 5135,5144 ****
char **argmodes = NULL;
char **argnames = NULL;
! /* Dump only funcs in dumpable namespaces */
! if (!finfo->dobj.namespace->dump || dataOnly)
return;
query = createPQExpBuffer();
q = createPQExpBuffer();
delqry = createPQExpBuffer();
--- 5163,5173 ----
char **argmodes = NULL;
char **argnames = NULL;
! /* Dump only funcs in dumpable namespaces, or needed language handlers */
! if ((!finfo->isProlangFunc && !finfo->dobj.namespace->dump) || dataOnly)
return;
+
query = createPQExpBuffer();
q = createPQExpBuffer();
delqry = createPQExpBuffer();
Index: src/bin/pg_dump/pg_dump.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v
retrieving revision 1.115
diff -c -r1.115 pg_dump.h
*** src/bin/pg_dump/pg_dump.h 31 Dec 2004 22:03:08 -0000 1.115
--- src/bin/pg_dump/pg_dump.h 28 Jun 2005 00:22:34 -0000
***************
*** 131,136 ****
--- 131,137 ----
Oid *argtypes;
Oid prorettype;
char *proacl;
+ bool isProlangFunc;
} FuncInfo;
/* AggInfo is a superset of FuncInfo */
Index: src/bin/scripts/createlang.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/scripts/createlang.c,v
retrieving revision 1.17
diff -c -r1.17 createlang.c
*** src/bin/scripts/createlang.c 22 Jun 2005 16:45:50 -0000 1.17
--- src/bin/scripts/createlang.c 28 Jun 2005 00:22:34 -0000
***************
*** 140,146 ****
conn = connectDatabase(dbname, host, port, username, password, progname);
! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE WHEN lanpltrusted THEN '%s' ELSE '%s' END) as \"%s\"
FROMpg_language WHERE lanispl IS TRUE;", _("Name"), _("yes"), _("no"), _("Trusted?"));
result = executeQuery(conn, sql.data, progname, echo);
memset(&popt, 0, sizeof(popt));
--- 140,149 ----
conn = connectDatabase(dbname, host, port, username, password, progname);
! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE WHEN lanpltrusted "
! "THEN '%s' ELSE '%s' END) as \"%s\" FROM pg_language "
! "WHERE lanispl IS TRUE;",
! _("Name"), _("yes"), _("no"), _("Trusted?"));
result = executeQuery(conn, sql.data, progname, echo);
memset(&popt, 0, sizeof(popt));
***************
*** 209,216 ****
}
else
{
! fprintf(stderr, _("%s: unsupported language \"%s\"\n"), progname, langname);
! fprintf(stderr, _("Supported languages are plpgsql, pltcl, pltclu, plperl, plperlu, and plpythonu.\n"));
exit(1);
}
--- 212,221 ----
}
else
{
! fprintf(stderr, _("%s: unsupported language \"%s\"\n"),
! progname, langname);
! fprintf(stderr, _("Supported languages are plpgsql, pltcl, pltclu, "
! "plperl, plperlu, and plpythonu.\n"));
exit(1);
}
***************
*** 219,231 ****
/*
* Make sure the language isn't already installed
*/
! printfPQExpBuffer(&sql, "SELECT oid FROM pg_language WHERE lanname = '%s';", langname);
result = executeQuery(conn, sql.data, progname, echo);
if (PQntuples(result) > 0)
{
PQfinish(conn);
fprintf(stderr,
! _("%s: language \"%s\" is already installed in database \"%s\"\n"),
progname, langname, dbname);
/* separate exit status for "already installed" */
exit(2);
--- 224,239 ----
/*
* Make sure the language isn't already installed
*/
! printfPQExpBuffer(&sql,
! "SELECT oid FROM pg_language WHERE lanname = '%s';",
! langname);
result = executeQuery(conn, sql.data, progname, echo);
if (PQntuples(result) > 0)
{
PQfinish(conn);
fprintf(stderr,
! _("%s: language \"%s\" is already installed in "
! "database \"%s\"\n"),
progname, langname, dbname);
/* separate exit status for "already installed" */
exit(2);
***************
*** 235,241 ****
/*
* Check whether the call handler exists
*/
! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s' AND prorettype =
'pg_catalog.language_handler'::regtypeAND pronargs = 0;", handler);
result = executeQuery(conn, sql.data, progname, echo);
handlerexists = (PQntuples(result) > 0);
PQclear(result);
--- 243,251 ----
/*
* Check whether the call handler exists
*/
! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s' "
! "AND prorettype = 'pg_catalog.language_handler'::regtype "
! "AND pronargs = 0;", handler);
result = executeQuery(conn, sql.data, progname, echo);
handlerexists = (PQntuples(result) > 0);
PQclear(result);
***************
*** 245,251 ****
*/
if (validator)
{
! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s' AND proargtypes[0] =
'pg_catalog.oid'::regtypeAND pronargs = 1;", validator);
result = executeQuery(conn, sql.data, progname, echo);
validatorexists = (PQntuples(result) > 0);
PQclear(result);
--- 255,263 ----
*/
if (validator)
{
! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s'"
! " AND proargtypes[0] = 'pg_catalog.oid'::regtype "
! " AND pronargs = 1;", validator);
result = executeQuery(conn, sql.data, progname, echo);
validatorexists = (PQntuples(result) > 0);
PQclear(result);
***************
*** 260,279 ****
if (!handlerexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION \"%s\" () RETURNS language_handler AS '%s/%s' LANGUAGE C;\n",
handler, pglib, object);
if (!validatorexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION \"%s\" (oid) RETURNS void AS '%s/%s' LANGUAGE C;\n",
validator, pglib, object);
appendPQExpBuffer(&sql,
! "CREATE %sLANGUAGE \"%s\" HANDLER \"%s\"",
(trusted ? "TRUSTED " : ""), langname, handler);
if (validator)
! appendPQExpBuffer(&sql, " VALIDATOR \"%s\"", validator);
appendPQExpBuffer(&sql, ";\n");
--- 272,293 ----
if (!handlerexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION pg_catalog.\"%s\" () RETURNS "
! "language_handler AS '%s/%s' LANGUAGE C;\n",
handler, pglib, object);
if (!validatorexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION pg_catalog.\"%s\" (oid) RETURNS "
! "void AS '%s/%s' LANGUAGE C;\n",
validator, pglib, object);
appendPQExpBuffer(&sql,
! "CREATE %sLANGUAGE \"%s\" HANDLER pg_catalog.\"%s\"",
(trusted ? "TRUSTED " : ""), langname, handler);
if (validator)
! appendPQExpBuffer(&sql, " VALIDATOR pg_catalog.\"%s\"", validator);
appendPQExpBuffer(&sql, ";\n");
Index: src/bin/scripts/droplang.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/scripts/droplang.c,v
retrieving revision 1.15
diff -c -r1.15 droplang.c
*** src/bin/scripts/droplang.c 14 Jun 2005 02:57:45 -0000 1.15
--- src/bin/scripts/droplang.c 28 Jun 2005 00:22:34 -0000
***************
*** 52,57 ****
--- 52,59 ----
Oid lanvalidator;
char *handler;
char *validator;
+ char *handler_ns;
+ char *validator_ns;
bool keephandler;
bool keepvalidator;
***************
*** 135,143 ****
{
printQueryOpt popt;
! conn = connectDatabase(dbname, host, port, username, password, progname);
! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE WHEN lanpltrusted THEN '%s' ELSE '%s' END) as \"%s\"
FROMpg_language WHERE lanispl IS TRUE;", _("Name"), _("yes"), _("no"), _("Trusted?"));
result = executeQuery(conn, sql.data, progname, echo);
memset(&popt, 0, sizeof(popt));
--- 137,149 ----
{
printQueryOpt popt;
! conn = connectDatabase(dbname, host, port, username, password,
! progname);
! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE "
! "WHEN lanpltrusted THEN '%s' ELSE '%s' END) "
! "as \"%s\" FROM pg_language WHERE lanispl IS TRUE;",
! _("Name"), _("yes"), _("no"), _("Trusted?"));
result = executeQuery(conn, sql.data, progname, echo);
memset(&popt, 0, sizeof(popt));
***************
*** 153,160 ****
if (langname == NULL)
{
! fprintf(stderr, _("%s: missing required argument language name\n"), progname);
! fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
}
--- 159,168 ----
if (langname == NULL)
{
! fprintf(stderr, _("%s: missing required argument language name\n"),
! progname);
! fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
! progname);
exit(1);
}
***************
*** 168,179 ****
* Make sure the language is installed and find the OIDs of the
* handler and validator functions
*/
! printfPQExpBuffer(&sql, "SELECT lanplcallfoid, lanvalidator FROM pg_language WHERE lanname = '%s' AND lanispl;",
langname);
result = executeQuery(conn, sql.data, progname, echo);
if (PQntuples(result) == 0)
{
PQfinish(conn);
! fprintf(stderr, _("%s: language \"%s\" is not installed in database \"%s\"\n"),
progname, langname, dbname);
exit(1);
}
--- 176,190 ----
* Make sure the language is installed and find the OIDs of the
* handler and validator functions
*/
! printfPQExpBuffer(&sql, "SELECT lanplcallfoid, lanvalidator "
! "FROM pg_language WHERE lanname = '%s' AND lanispl;",
! langname);
result = executeQuery(conn, sql.data, progname, echo);
if (PQntuples(result) == 0)
{
PQfinish(conn);
! fprintf(stderr, _("%s: language \"%s\" is not installed in "
! "database \"%s\"\n"),
progname, langname, dbname);
exit(1);
}
***************
*** 184,196 ****
/*
* Check that there are no functions left defined in that language
*/
! printfPQExpBuffer(&sql, "SELECT count(proname) FROM pg_proc P, pg_language L WHERE P.prolang = L.oid AND
L.lanname= '%s';", langname);
result = executeQuery(conn, sql.data, progname, echo);
if (strcmp(PQgetvalue(result, 0, 0), "0") != 0)
{
PQfinish(conn);
fprintf(stderr,
! _("%s: still %s functions declared in language \"%s\"; language not removed\n"),
progname, PQgetvalue(result, 0, 0), langname);
exit(1);
}
--- 195,210 ----
/*
* Check that there are no functions left defined in that language
*/
! printfPQExpBuffer(&sql, "SELECT count(proname) FROM pg_proc P, "
! "pg_language L WHERE P.prolang = L.oid "
! "AND L.lanname = '%s';", langname);
result = executeQuery(conn, sql.data, progname, echo);
if (strcmp(PQgetvalue(result, 0, 0), "0") != 0)
{
PQfinish(conn);
fprintf(stderr,
! _("%s: still %s functions declared in language \"%s\"; "
! "language not removed\n"),
progname, PQgetvalue(result, 0, 0), langname);
exit(1);
}
***************
*** 199,205 ****
/*
* Check that the handler function isn't used by some other language
*/
! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language WHERE lanplcallfoid = %u AND lanname <> '%s';",
lanplcallfoid,langname);
result = executeQuery(conn, sql.data, progname, echo);
if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
keephandler = false;
--- 213,221 ----
/*
* Check that the handler function isn't used by some other language
*/
! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language "
! "WHERE lanplcallfoid = %u AND lanname <> '%s';",
! lanplcallfoid, langname);
result = executeQuery(conn, sql.data, progname, echo);
if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
keephandler = false;
***************
*** 212,231 ****
*/
if (!keephandler)
{
! printfPQExpBuffer(&sql, "SELECT proname FROM pg_proc WHERE oid = %u;", lanplcallfoid);
result = executeQuery(conn, sql.data, progname, echo);
handler = strdup(PQgetvalue(result, 0, 0));
PQclear(result);
}
else
handler = NULL;
/*
* Check that the validator function isn't used by some other language
*/
if (OidIsValid(lanvalidator))
{
! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language WHERE lanvalidator = %u AND lanname <> '%s';",
lanvalidator,langname);
result = executeQuery(conn, sql.data, progname, echo);
if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
keepvalidator = false;
--- 228,256 ----
*/
if (!keephandler)
{
! printfPQExpBuffer(&sql, "SELECT proname, (SELECT nspname "
! "FROM pg_namespace ns WHERE ns.oid = pronamespace) "
! "AS prons FROM pg_proc WHERE oid = %u;",
! lanplcallfoid);
result = executeQuery(conn, sql.data, progname, echo);
handler = strdup(PQgetvalue(result, 0, 0));
+ handler_ns = strdup(PQgetvalue(result, 0, 1));
PQclear(result);
}
else
+ {
handler = NULL;
+ handler_ns = NULL;
+ }
/*
* Check that the validator function isn't used by some other language
*/
if (OidIsValid(lanvalidator))
{
! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language WHERE "
! "lanvalidator = %u AND lanname <> '%s';",
! lanvalidator, langname);
result = executeQuery(conn, sql.data, progname, echo);
if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
keepvalidator = false;
***************
*** 241,262 ****
*/
if (!keepvalidator)
{
! printfPQExpBuffer(&sql, "SELECT proname FROM pg_proc WHERE oid = %u;", lanvalidator);
result = executeQuery(conn, sql.data, progname, echo);
validator = strdup(PQgetvalue(result, 0, 0));
PQclear(result);
}
else
validator = NULL;
/*
* Drop the language and the functions
*/
printfPQExpBuffer(&sql, "DROP LANGUAGE \"%s\";\n", langname);
if (!keephandler)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\" ();\n", handler);
if (!keepvalidator)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\" (oid);\n", validator);
if (echo)
printf("%s", sql.data);
result = PQexec(conn, sql.data);
--- 266,296 ----
*/
if (!keepvalidator)
{
! printfPQExpBuffer(&sql, "SELECT proname, (SELECT nspname "
! "FROM pg_namespace ns WHERE ns.oid = pronamespace) "
! "AS prons FROM pg_proc WHERE oid = %u;",
! lanvalidator);
result = executeQuery(conn, sql.data, progname, echo);
validator = strdup(PQgetvalue(result, 0, 0));
+ validator_ns = strdup(PQgetvalue(result, 0, 1));
PQclear(result);
}
else
+ {
validator = NULL;
+ validator_ns = NULL;
+ }
/*
* Drop the language and the functions
*/
printfPQExpBuffer(&sql, "DROP LANGUAGE \"%s\";\n", langname);
if (!keephandler)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\".\"%s\" ();\n",
! handler_ns, handler);
if (!keepvalidator)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\".\"%s\" (oid);\n",
! validator_ns, validator);
if (echo)
printf("%s", sql.data);
result = PQexec(conn, sql.data);
Patch applied. Thanks.
---------------------------------------------------------------------------
Andrew Dunstan wrote:
>
> This patch implements putting language handlers for the optional PLs
> into pg_catalog rather than public, and supports dumping languages whose
> handlers are found there. This will make it easier to drop the public
> schema if desired.
>
> Unlike the previous patch, the comments have been updated and I have
> reformatted some code to meet Alvarro's request to stick to 80 cols. (I
> actually aghree with this - it makes printing the code much nicer).
>
> I think I did the right thing w.r.t versions earlier than 7.3, but I
> have no real way of checking, so that should be checked by someone with
> more/older knowledge than me ;-)
>
> cheers
>
> andrew
>
> Index: src/bin/pg_dump/pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.410
> diff -c -r1.410 pg_dump.c
> *** src/bin/pg_dump/pg_dump.c 21 Jun 2005 20:45:44 -0000 1.410
> --- src/bin/pg_dump/pg_dump.c 28 Jun 2005 00:22:34 -0000
> ***************
> *** 2146,2151 ****
> --- 2146,2152 ----
> int i_proargtypes;
> int i_prorettype;
> int i_proacl;
> + int i_is_pl_handler;
>
> /* Make sure we are in proper schema */
> selectSourceSchema("pg_catalog");
> ***************
> *** 2154,2168 ****
>
> if (g_fout->remoteVersion >= 70300)
> {
> appendPQExpBuffer(query,
> "SELECT tableoid, oid, proname, prolang, "
> "pronargs, proargtypes, prorettype, proacl, "
> "pronamespace, "
> ! "(select usename from pg_user where proowner = usesysid) as usename "
> "FROM pg_proc "
> "WHERE NOT proisagg "
> ! "AND pronamespace != "
> ! "(select oid from pg_namespace where nspname = 'pg_catalog')");
> }
> else if (g_fout->remoteVersion >= 70100)
> {
> --- 2155,2190 ----
>
> if (g_fout->remoteVersion >= 70300)
> {
> + /*
> + * We now collect info on pg_catalog resident functions, but
> + * only if they are language call handlers or validators, and
> + * only for non-default languages (i.e. not internal/C/SQL).
> + */
> appendPQExpBuffer(query,
> "SELECT tableoid, oid, proname, prolang, "
> "pronargs, proargtypes, prorettype, proacl, "
> "pronamespace, "
> ! "(select usename from pg_user "
> ! " where proowner = usesysid) as usename, "
> ! "CASE WHEN oid IN "
> ! " (select lanplcallfoid from pg_language "
> ! " where lanplcallfoid != 0) THEN true "
> ! " WHEN oid IN "
> ! " (select lanvalidator from pg_language "
> ! " where lanplcallfoid != 0) THEN true "
> ! " ELSE false END AS is_pl_handler "
> "FROM pg_proc "
> "WHERE NOT proisagg "
> ! "AND (pronamespace != "
> ! " (select oid from pg_namespace "
> ! " where nspname = 'pg_catalog')"
> ! " OR oid IN "
> ! " (select lanplcallfoid from pg_language "
> ! " where lanplcallfoid != 0) "
> ! " OR oid IN "
> ! " (select lanvalidator from pg_language "
> ! " where lanplcallfoid != 0))"
> ! );
> }
> else if (g_fout->remoteVersion >= 70100)
> {
> ***************
> *** 2171,2177 ****
> "pronargs, proargtypes, prorettype, "
> "'{=X}' as proacl, "
> "0::oid as pronamespace, "
> ! "(select usename from pg_user where proowner = usesysid) as usename "
> "FROM pg_proc "
> "where pg_proc.oid > '%u'::oid",
> g_last_builtin_oid);
> --- 2193,2201 ----
> "pronargs, proargtypes, prorettype, "
> "'{=X}' as proacl, "
> "0::oid as pronamespace, "
> ! "(select usename from pg_user "
> ! " where proowner = usesysid) as usename, "
> ! "false AS is_pl_handler "
> "FROM pg_proc "
> "where pg_proc.oid > '%u'::oid",
> g_last_builtin_oid);
> ***************
> *** 2180,2191 ****
> {
> appendPQExpBuffer(query,
> "SELECT "
> ! "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') AS tableoid, "
> "oid, proname, prolang, "
> "pronargs, proargtypes, prorettype, "
> "'{=X}' as proacl, "
> "0::oid as pronamespace, "
> ! "(select usename from pg_user where proowner = usesysid) as usename "
> "FROM pg_proc "
> "where pg_proc.oid > '%u'::oid",
> g_last_builtin_oid);
> --- 2204,2218 ----
> {
> appendPQExpBuffer(query,
> "SELECT "
> ! "(SELECT oid FROM pg_class "
> ! " WHERE relname = 'pg_proc') AS tableoid, "
> "oid, proname, prolang, "
> "pronargs, proargtypes, prorettype, "
> "'{=X}' as proacl, "
> "0::oid as pronamespace, "
> ! "(select usename from pg_user "
> ! " where proowner = usesysid) as usename, "
> ! "false AS is_pl_handler "
> "FROM pg_proc "
> "where pg_proc.oid > '%u'::oid",
> g_last_builtin_oid);
> ***************
> *** 2210,2215 ****
> --- 2237,2243 ----
> i_proargtypes = PQfnumber(res, "proargtypes");
> i_prorettype = PQfnumber(res, "prorettype");
> i_proacl = PQfnumber(res, "proacl");
> + i_is_pl_handler = PQfnumber(res,"is_pl_handler");
>
> for (i = 0; i < ntups; i++)
> {
> ***************
> *** 2218,2230 ****
> finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
> AssignDumpId(&finfo[i].dobj);
> finfo[i].dobj.name = strdup(PQgetvalue(res, i, i_proname));
> ! finfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
> finfo[i].dobj.catId.oid);
> finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
> finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
> finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
> finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
> finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
> if (finfo[i].nargs == 0)
> finfo[i].argtypes = NULL;
> else
> --- 2246,2261 ----
> finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
> AssignDumpId(&finfo[i].dobj);
> finfo[i].dobj.name = strdup(PQgetvalue(res, i, i_proname));
> ! finfo[i].dobj.namespace =
> ! findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
> finfo[i].dobj.catId.oid);
> finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
> finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
> finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
> finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
> finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
> + finfo[i].isProlangFunc =
> + strcmp(PQgetvalue(res, i, i_is_pl_handler), "t") == 0;
> if (finfo[i].nargs == 0)
> finfo[i].argtypes = NULL;
> else
> ***************
> *** 2235,2241 ****
> }
>
> if (strlen(finfo[i].usename) == 0)
> ! write_msg(NULL, "WARNING: owner of function \"%s\" appears to be invalid\n",
> finfo[i].dobj.name);
> }
>
> --- 2266,2273 ----
> }
>
> if (strlen(finfo[i].usename) == 0)
> ! write_msg(NULL,
> ! "WARNING: owner of function \"%s\" appears to be invalid\n",
> finfo[i].dobj.name);
> }
>
> ***************
> *** 4921,4943 ****
> return;
>
> /*
> ! * Current theory is to dump PLs iff their underlying functions will
> ! * be dumped (are in a dumpable namespace, or have a non-system OID in
> ! * pre-7.3 databases). Actually, we treat the PL itself as being in
> * the underlying function's namespace, though it isn't really. This
> * avoids searchpath problems for the HANDLER clause.
> *
> - * If the underlying function is in the pg_catalog namespace, we won't
> - * have loaded it into finfo[] at all; therefore, treat failure to
> - * find it in finfo[] as indicating we shouldn't dump it, not as an
> - * error condition. Ditto for the validator.
> */
>
> funcInfo = findFuncByOid(plang->lanplcallfoid);
> if (funcInfo == NULL)
> return;
>
> ! if (!funcInfo->dobj.namespace->dump)
> return;
>
> if (OidIsValid(plang->lanvalidator))
> --- 4953,4971 ----
> return;
>
> /*
> ! * We dump PLs iff their underlying call handler functions have been
> ! * marked as language functions (or have a non-system OID in
> ! * pre-7.3 databases). We treat the PL itself as being in
> * the underlying function's namespace, though it isn't really. This
> * avoids searchpath problems for the HANDLER clause.
> *
> */
>
> funcInfo = findFuncByOid(plang->lanplcallfoid);
> if (funcInfo == NULL)
> return;
>
> ! if (!funcInfo->isProlangFunc && !funcInfo->dobj.namespace->dump)
> return;
>
> if (OidIsValid(plang->lanvalidator))
> ***************
> *** 5135,5144 ****
> char **argmodes = NULL;
> char **argnames = NULL;
>
> ! /* Dump only funcs in dumpable namespaces */
> ! if (!finfo->dobj.namespace->dump || dataOnly)
> return;
>
> query = createPQExpBuffer();
> q = createPQExpBuffer();
> delqry = createPQExpBuffer();
> --- 5163,5173 ----
> char **argmodes = NULL;
> char **argnames = NULL;
>
> ! /* Dump only funcs in dumpable namespaces, or needed language handlers */
> ! if ((!finfo->isProlangFunc && !finfo->dobj.namespace->dump) || dataOnly)
> return;
>
> +
> query = createPQExpBuffer();
> q = createPQExpBuffer();
> delqry = createPQExpBuffer();
> Index: src/bin/pg_dump/pg_dump.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v
> retrieving revision 1.115
> diff -c -r1.115 pg_dump.h
> *** src/bin/pg_dump/pg_dump.h 31 Dec 2004 22:03:08 -0000 1.115
> --- src/bin/pg_dump/pg_dump.h 28 Jun 2005 00:22:34 -0000
> ***************
> *** 131,136 ****
> --- 131,137 ----
> Oid *argtypes;
> Oid prorettype;
> char *proacl;
> + bool isProlangFunc;
> } FuncInfo;
>
> /* AggInfo is a superset of FuncInfo */
> Index: src/bin/scripts/createlang.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/bin/scripts/createlang.c,v
> retrieving revision 1.17
> diff -c -r1.17 createlang.c
> *** src/bin/scripts/createlang.c 22 Jun 2005 16:45:50 -0000 1.17
> --- src/bin/scripts/createlang.c 28 Jun 2005 00:22:34 -0000
> ***************
> *** 140,146 ****
>
> conn = connectDatabase(dbname, host, port, username, password, progname);
>
> ! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE WHEN lanpltrusted THEN '%s' ELSE '%s' END) as
\"%s\"FROM pg_language WHERE lanispl IS TRUE;", _("Name"), _("yes"), _("no"), _("Trusted?"));
> result = executeQuery(conn, sql.data, progname, echo);
>
> memset(&popt, 0, sizeof(popt));
> --- 140,149 ----
>
> conn = connectDatabase(dbname, host, port, username, password, progname);
>
> ! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE WHEN lanpltrusted "
> ! "THEN '%s' ELSE '%s' END) as \"%s\" FROM pg_language "
> ! "WHERE lanispl IS TRUE;",
> ! _("Name"), _("yes"), _("no"), _("Trusted?"));
> result = executeQuery(conn, sql.data, progname, echo);
>
> memset(&popt, 0, sizeof(popt));
> ***************
> *** 209,216 ****
> }
> else
> {
> ! fprintf(stderr, _("%s: unsupported language \"%s\"\n"), progname, langname);
> ! fprintf(stderr, _("Supported languages are plpgsql, pltcl, pltclu, plperl, plperlu, and plpythonu.\n"));
> exit(1);
> }
>
> --- 212,221 ----
> }
> else
> {
> ! fprintf(stderr, _("%s: unsupported language \"%s\"\n"),
> ! progname, langname);
> ! fprintf(stderr, _("Supported languages are plpgsql, pltcl, pltclu, "
> ! "plperl, plperlu, and plpythonu.\n"));
> exit(1);
> }
>
> ***************
> *** 219,231 ****
> /*
> * Make sure the language isn't already installed
> */
> ! printfPQExpBuffer(&sql, "SELECT oid FROM pg_language WHERE lanname = '%s';", langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (PQntuples(result) > 0)
> {
> PQfinish(conn);
> fprintf(stderr,
> ! _("%s: language \"%s\" is already installed in database \"%s\"\n"),
> progname, langname, dbname);
> /* separate exit status for "already installed" */
> exit(2);
> --- 224,239 ----
> /*
> * Make sure the language isn't already installed
> */
> ! printfPQExpBuffer(&sql,
> ! "SELECT oid FROM pg_language WHERE lanname = '%s';",
> ! langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (PQntuples(result) > 0)
> {
> PQfinish(conn);
> fprintf(stderr,
> ! _("%s: language \"%s\" is already installed in "
> ! "database \"%s\"\n"),
> progname, langname, dbname);
> /* separate exit status for "already installed" */
> exit(2);
> ***************
> *** 235,241 ****
> /*
> * Check whether the call handler exists
> */
> ! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s' AND prorettype =
'pg_catalog.language_handler'::regtypeAND pronargs = 0;", handler);
> result = executeQuery(conn, sql.data, progname, echo);
> handlerexists = (PQntuples(result) > 0);
> PQclear(result);
> --- 243,251 ----
> /*
> * Check whether the call handler exists
> */
> ! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s' "
> ! "AND prorettype = 'pg_catalog.language_handler'::regtype "
> ! "AND pronargs = 0;", handler);
> result = executeQuery(conn, sql.data, progname, echo);
> handlerexists = (PQntuples(result) > 0);
> PQclear(result);
> ***************
> *** 245,251 ****
> */
> if (validator)
> {
> ! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s' AND proargtypes[0] =
'pg_catalog.oid'::regtypeAND pronargs = 1;", validator);
> result = executeQuery(conn, sql.data, progname, echo);
> validatorexists = (PQntuples(result) > 0);
> PQclear(result);
> --- 255,263 ----
> */
> if (validator)
> {
> ! printfPQExpBuffer(&sql, "SELECT oid FROM pg_proc WHERE proname = '%s'"
> ! " AND proargtypes[0] = 'pg_catalog.oid'::regtype "
> ! " AND pronargs = 1;", validator);
> result = executeQuery(conn, sql.data, progname, echo);
> validatorexists = (PQntuples(result) > 0);
> PQclear(result);
> ***************
> *** 260,279 ****
>
> if (!handlerexists)
> appendPQExpBuffer(&sql,
> ! "CREATE FUNCTION \"%s\" () RETURNS language_handler AS '%s/%s' LANGUAGE C;\n",
> handler, pglib, object);
>
> if (!validatorexists)
> appendPQExpBuffer(&sql,
> ! "CREATE FUNCTION \"%s\" (oid) RETURNS void AS '%s/%s' LANGUAGE C;\n",
> validator, pglib, object);
>
> appendPQExpBuffer(&sql,
> ! "CREATE %sLANGUAGE \"%s\" HANDLER \"%s\"",
> (trusted ? "TRUSTED " : ""), langname, handler);
>
> if (validator)
> ! appendPQExpBuffer(&sql, " VALIDATOR \"%s\"", validator);
>
> appendPQExpBuffer(&sql, ";\n");
>
> --- 272,293 ----
>
> if (!handlerexists)
> appendPQExpBuffer(&sql,
> ! "CREATE FUNCTION pg_catalog.\"%s\" () RETURNS "
> ! "language_handler AS '%s/%s' LANGUAGE C;\n",
> handler, pglib, object);
>
> if (!validatorexists)
> appendPQExpBuffer(&sql,
> ! "CREATE FUNCTION pg_catalog.\"%s\" (oid) RETURNS "
> ! "void AS '%s/%s' LANGUAGE C;\n",
> validator, pglib, object);
>
> appendPQExpBuffer(&sql,
> ! "CREATE %sLANGUAGE \"%s\" HANDLER pg_catalog.\"%s\"",
> (trusted ? "TRUSTED " : ""), langname, handler);
>
> if (validator)
> ! appendPQExpBuffer(&sql, " VALIDATOR pg_catalog.\"%s\"", validator);
>
> appendPQExpBuffer(&sql, ";\n");
>
> Index: src/bin/scripts/droplang.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/bin/scripts/droplang.c,v
> retrieving revision 1.15
> diff -c -r1.15 droplang.c
> *** src/bin/scripts/droplang.c 14 Jun 2005 02:57:45 -0000 1.15
> --- src/bin/scripts/droplang.c 28 Jun 2005 00:22:34 -0000
> ***************
> *** 52,57 ****
> --- 52,59 ----
> Oid lanvalidator;
> char *handler;
> char *validator;
> + char *handler_ns;
> + char *validator_ns;
> bool keephandler;
> bool keepvalidator;
>
> ***************
> *** 135,143 ****
> {
> printQueryOpt popt;
>
> ! conn = connectDatabase(dbname, host, port, username, password, progname);
>
> ! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE WHEN lanpltrusted THEN '%s' ELSE '%s' END) as
\"%s\"FROM pg_language WHERE lanispl IS TRUE;", _("Name"), _("yes"), _("no"), _("Trusted?"));
> result = executeQuery(conn, sql.data, progname, echo);
>
> memset(&popt, 0, sizeof(popt));
> --- 137,149 ----
> {
> printQueryOpt popt;
>
> ! conn = connectDatabase(dbname, host, port, username, password,
> ! progname);
>
> ! printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", (CASE "
> ! "WHEN lanpltrusted THEN '%s' ELSE '%s' END) "
> ! "as \"%s\" FROM pg_language WHERE lanispl IS TRUE;",
> ! _("Name"), _("yes"), _("no"), _("Trusted?"));
> result = executeQuery(conn, sql.data, progname, echo);
>
> memset(&popt, 0, sizeof(popt));
> ***************
> *** 153,160 ****
>
> if (langname == NULL)
> {
> ! fprintf(stderr, _("%s: missing required argument language name\n"), progname);
> ! fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
> exit(1);
> }
>
> --- 159,168 ----
>
> if (langname == NULL)
> {
> ! fprintf(stderr, _("%s: missing required argument language name\n"),
> ! progname);
> ! fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
> ! progname);
> exit(1);
> }
>
> ***************
> *** 168,179 ****
> * Make sure the language is installed and find the OIDs of the
> * handler and validator functions
> */
> ! printfPQExpBuffer(&sql, "SELECT lanplcallfoid, lanvalidator FROM pg_language WHERE lanname = '%s' AND
lanispl;",langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (PQntuples(result) == 0)
> {
> PQfinish(conn);
> ! fprintf(stderr, _("%s: language \"%s\" is not installed in database \"%s\"\n"),
> progname, langname, dbname);
> exit(1);
> }
> --- 176,190 ----
> * Make sure the language is installed and find the OIDs of the
> * handler and validator functions
> */
> ! printfPQExpBuffer(&sql, "SELECT lanplcallfoid, lanvalidator "
> ! "FROM pg_language WHERE lanname = '%s' AND lanispl;",
> ! langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (PQntuples(result) == 0)
> {
> PQfinish(conn);
> ! fprintf(stderr, _("%s: language \"%s\" is not installed in "
> ! "database \"%s\"\n"),
> progname, langname, dbname);
> exit(1);
> }
> ***************
> *** 184,196 ****
> /*
> * Check that there are no functions left defined in that language
> */
> ! printfPQExpBuffer(&sql, "SELECT count(proname) FROM pg_proc P, pg_language L WHERE P.prolang = L.oid AND
L.lanname= '%s';", langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (strcmp(PQgetvalue(result, 0, 0), "0") != 0)
> {
> PQfinish(conn);
> fprintf(stderr,
> ! _("%s: still %s functions declared in language \"%s\"; language not removed\n"),
> progname, PQgetvalue(result, 0, 0), langname);
> exit(1);
> }
> --- 195,210 ----
> /*
> * Check that there are no functions left defined in that language
> */
> ! printfPQExpBuffer(&sql, "SELECT count(proname) FROM pg_proc P, "
> ! "pg_language L WHERE P.prolang = L.oid "
> ! "AND L.lanname = '%s';", langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (strcmp(PQgetvalue(result, 0, 0), "0") != 0)
> {
> PQfinish(conn);
> fprintf(stderr,
> ! _("%s: still %s functions declared in language \"%s\"; "
> ! "language not removed\n"),
> progname, PQgetvalue(result, 0, 0), langname);
> exit(1);
> }
> ***************
> *** 199,205 ****
> /*
> * Check that the handler function isn't used by some other language
> */
> ! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language WHERE lanplcallfoid = %u AND lanname <> '%s';",
lanplcallfoid,langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
> keephandler = false;
> --- 213,221 ----
> /*
> * Check that the handler function isn't used by some other language
> */
> ! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language "
> ! "WHERE lanplcallfoid = %u AND lanname <> '%s';",
> ! lanplcallfoid, langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
> keephandler = false;
> ***************
> *** 212,231 ****
> */
> if (!keephandler)
> {
> ! printfPQExpBuffer(&sql, "SELECT proname FROM pg_proc WHERE oid = %u;", lanplcallfoid);
> result = executeQuery(conn, sql.data, progname, echo);
> handler = strdup(PQgetvalue(result, 0, 0));
> PQclear(result);
> }
> else
> handler = NULL;
>
> /*
> * Check that the validator function isn't used by some other language
> */
> if (OidIsValid(lanvalidator))
> {
> ! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language WHERE lanvalidator = %u AND lanname <> '%s';",
lanvalidator,langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
> keepvalidator = false;
> --- 228,256 ----
> */
> if (!keephandler)
> {
> ! printfPQExpBuffer(&sql, "SELECT proname, (SELECT nspname "
> ! "FROM pg_namespace ns WHERE ns.oid = pronamespace) "
> ! "AS prons FROM pg_proc WHERE oid = %u;",
> ! lanplcallfoid);
> result = executeQuery(conn, sql.data, progname, echo);
> handler = strdup(PQgetvalue(result, 0, 0));
> + handler_ns = strdup(PQgetvalue(result, 0, 1));
> PQclear(result);
> }
> else
> + {
> handler = NULL;
> + handler_ns = NULL;
> + }
>
> /*
> * Check that the validator function isn't used by some other language
> */
> if (OidIsValid(lanvalidator))
> {
> ! printfPQExpBuffer(&sql, "SELECT count(*) FROM pg_language WHERE "
> ! "lanvalidator = %u AND lanname <> '%s';",
> ! lanvalidator, langname);
> result = executeQuery(conn, sql.data, progname, echo);
> if (strcmp(PQgetvalue(result, 0, 0), "0") == 0)
> keepvalidator = false;
> ***************
> *** 241,262 ****
> */
> if (!keepvalidator)
> {
> ! printfPQExpBuffer(&sql, "SELECT proname FROM pg_proc WHERE oid = %u;", lanvalidator);
> result = executeQuery(conn, sql.data, progname, echo);
> validator = strdup(PQgetvalue(result, 0, 0));
> PQclear(result);
> }
> else
> validator = NULL;
>
> /*
> * Drop the language and the functions
> */
> printfPQExpBuffer(&sql, "DROP LANGUAGE \"%s\";\n", langname);
> if (!keephandler)
> ! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\" ();\n", handler);
> if (!keepvalidator)
> ! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\" (oid);\n", validator);
> if (echo)
> printf("%s", sql.data);
> result = PQexec(conn, sql.data);
> --- 266,296 ----
> */
> if (!keepvalidator)
> {
> ! printfPQExpBuffer(&sql, "SELECT proname, (SELECT nspname "
> ! "FROM pg_namespace ns WHERE ns.oid = pronamespace) "
> ! "AS prons FROM pg_proc WHERE oid = %u;",
> ! lanvalidator);
> result = executeQuery(conn, sql.data, progname, echo);
> validator = strdup(PQgetvalue(result, 0, 0));
> + validator_ns = strdup(PQgetvalue(result, 0, 1));
> PQclear(result);
> }
> else
> + {
> validator = NULL;
> + validator_ns = NULL;
> + }
>
> /*
> * Drop the language and the functions
> */
> printfPQExpBuffer(&sql, "DROP LANGUAGE \"%s\";\n", langname);
> if (!keephandler)
> ! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\".\"%s\" ();\n",
> ! handler_ns, handler);
> if (!keepvalidator)
> ! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\".\"%s\" (oid);\n",
> ! validator_ns, validator);
> if (echo)
> printf("%s", sql.data);
> result = PQexec(conn, sql.data);
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073