Re: Lost plpgsql function

> Never mind, I was able to duplicate it here. It looks like 7.0 is not
> consistent with later versions about what it keeps in pg_database.datpath.
> I'll have to go back and see what it's doing exactly ...

Attached is the patch for 7.4.*, if you need it.

regards, tom lane

Index: pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.355.2.1
diff -c -r1.355.2.1 pg_dump.c
*** pg_dump.c 19 Dec 2003 14:21:43 -0000 1.355.2.1
--- pg_dump.c 22 Jan 2004 19:06:19 -0000
***************
*** 1144,1154 ****
selectSourceSchema("pg_catalog");

/* Get the database owner and parameters from pg_database */
! appendPQExpBuffer(dbQry, "select (select usename from pg_user where usesysid = datdba) as dba,"
! " pg_encoding_to_char(encoding) as encoding,"
! " datpath from pg_database"
! " where datname = ");
! appendStringLiteral(dbQry, datname, true);

res = PQexec(g_conn, dbQry->data);
if (!res ||
--- 1144,1176 ----
selectSourceSchema("pg_catalog");

/* Get the database owner and parameters from pg_database */
! if (g_fout->remoteVersion >= 70100)
! {
! appendPQExpBuffer(dbQry, "SELECT "
! "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
! "pg_encoding_to_char(encoding) as encoding, "
! "datpath "
! "FROM pg_database "
! "WHERE datname = ");
! appendStringLiteral(dbQry, datname, true);
! }
! else
! {
! /*
! * In 7.0, datpath is either the same as datname, or the user-given
! * location with "/" and the datname appended. We must strip this
! * junk off to produce a correct LOCATION value.
! */
! appendPQExpBuffer(dbQry, "SELECT "
! "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
! "pg_encoding_to_char(encoding) as encoding, "
! "CASE WHEN length(datpath) > length(datname) THEN "
! "substr(datpath,1,length(datpath)-length(datname)-1) "
! "ELSE '' END as datpath "
! "FROM pg_database "
! "WHERE datname = ");
! appendStringLiteral(dbQry, datname, true);
! }

res = PQexec(g_conn, dbQry->data);
if (!res ||
Index: pg_dumpall.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.28
diff -c -r1.28 pg_dumpall.c
*** pg_dumpall.c 23 Sep 2003 22:48:53 -0000 1.28
--- pg_dumpall.c 22 Jan 2004 19:06:20 -0000
***************
*** 430,435 ****
--- 430,439 ----
else
{
/*
+ * In 7.0, datpath is either the same as datname, or the user-given
+ * location with "/" and the datname appended. We must strip this
+ * junk off to produce a correct LOCATION value.
+ *
* Note: 7.0 fails to cope with sub-select in COALESCE, so just
* deal with getting a NULL by not printing any OWNER clause.
*/
***************
*** 437,443 ****
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
! "'f' as datistemplate, datpath, '' as datacl "
"FROM pg_database d "
"ORDER BY 1");
}
--- 441,451 ----
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
! "'f' as datistemplate, "
! "CASE WHEN length(datpath) > length(datname) THEN "
! "substr(datpath,1,length(datpath)-length(datname)-1) "
! "ELSE '' END as datpath, "
! "'' as datacl "
"FROM pg_database d "
"ORDER BY 1");
}