Re: Lost plpgsql function - Mailing list pgsql-general

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