Date: Mon, 23 Oct 2006 21:43:47 +0200
From: Zdenek Kotala <Zdenek.Kotala@Sun.COM>
Subject: COPY does not work with regproc and aclitem
To: pgsql-hackers@postgresql.org
Message-id: <453D1B73.2050906@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
User-Agent: Mail/News 1.5 (X11/20051211)

I tried to use COPY command to export and import tables from catalog, 
but COPY command has problem with data type regproc. See example

   create table test (like pg_aggregate);
   copy pg_aggregate to '/tmp/pg_agg.out';
   copy test from '/tmp/pg_agg.out';

ERROR:  more than one function named "pg_catalog.avg"
CONTEXT:  COPY test, line 1, column aggfnoid: "pg_catalog.avg"


The problem is that pg_proc table has following unique indexes:

  "pg_proc_oid_index" UNIQUE, btree (oid)
  "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, proargtypes, 
pronamespace)

And regprocin in the backend/utils/adt/regproc.c cannot found unique OID 
for proname.

Workaround is use binary mode, but on other side aclitem is not 
supported in the binary mode.

   postgres=# copy pg_class to '/tmp/pg_class.out' binary;
   ERROR:  no binary output function available for type aclitem


The solution is that COPY command will be use OID instead procname for 
export regproc.


		Zdenek