OK, we figured it out. The problem is the documentation confused me!!! In man page of pg_restore: -P function-name --function=function name Specify a procedure or function to be restored.
User will assume that syntax of restoring a function is same as restoring a table, but it's not true, it's slightly different. To restore a table: pg_restore -Rxt mytable -d mydb2 dbf works, but to restore a function: pg_restore -P myfunction -d mydb2 dbf won't work, and you need to use: pg_restore -P "\"myfunction\" (args and type)" -d mydb2 dbf to make it work!!!!! I believe that the man page of pg_restore should be improved. Thanks. Jie Liang -----Original Message----- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 11:14 AM To: Jie Liang Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function Jie Liang wrote: > > Oops,my OS is FreeBSD4.3 PostgreSQL7.2 I cannot see such an error message in the pg_restore sources at all. Are you sure to use the right versions together? Jan > > Thanks > > Jie Liang > > -----Original Message----- > From: Jie Liang > Sent: Friday, June 28, 2002 1:46 PM > To: 'Jan Wieck' > Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: RE: [SQL] pg_restore cannot restore function > > No any error msg in the logfile, I didn't see any create function statement > in my logfile which I enabled the query log. > This function is written in PL/pgSQL which is enabled in target db, > If I pg_dump the schema into a plain text file, I can see its defination > there, I can easily copy & paste (restore) it into mydb2. > however, I failed to restore it by using flag -P with compressed file. > I also tried to use > su postgres -c "/usr/local/pgsql/bin/pg_restore --function=myfunction > --dbname=mydb2 dbf" > error msg > pg_restore: [archiver] could not open input file: No such file or directory > > weird??? > > I use > pg_restore -Rxt mytable -d mydb2 dbf > have no such a problem, it works. > > Is any syntax error?? > I am confused by documentation now! > Is it a bug???? > > Thanks > > Jie Liang > > -----Original Message----- > From: Jan Wieck [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 28, 2002 12:39 PM > To: Jie Liang > Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: Re: [SQL] pg_restore cannot restore function > > Jie Liang wrote: > > > > I use > > pg_dump -Fc mydb > dbf > > then I create another db by: > > createdb mydb2 > > I use > > pg_restore -P myfunction -d mydb2 dbf > > > > cannot restore myfunction into mydb2 > > > > why?????? > > Good question. Is there any error message in the postmaster log? > > If the function is written in a procedural language, is that language > enabled in the target database? If the function is written in the SQL > language, do all underlying objects like tables and views exist? If it's > a C language function, does the shared object containing the function > exist at the expected location? > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== [EMAIL PROTECTED] # -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] # ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org