Re: [SQL] pg_restore cannot restore function

OK, I have researched your problem, and we had several bugs in this
area. First, we quoted the function name in the storage tag, which
wasn't necessary and was inconsistent with other objects. Second, we
quoted all storage tags for GRANT/REVOKE entries, so restoring a table
without quotes would restore the table without the GRANT/REVOKE part.
And third, the documentation wasn't clear.

The following patch fixes all of these, and it will be in 7.3. The
patch probably will not work in 7.2.1 because we have added schemas to
the code, but you can probably manually apply it if needed.

Thanks for the report.

---------------------------------------------------------------------------

Jie Liang wrote:
>
> 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:JanWieck(at)Yahoo(dot)com]
> Sent: Monday, July 01, 2002 11:14 AM
> To: Jie Liang
> Cc: 'Bruce Momjian'; 'admin(at)postgresql(dot)org'; 'pgsql-sql(at)postgresql(dot)org'
> 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'; 'admin(at)postgresql(dot)org'; 'pgsql-sql(at)postgresql(dot)org'
> > 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:JanWieck(at)Yahoo(dot)com]
> > Sent: Friday, June 28, 2002 12:39 PM
> > To: Jie Liang
> > Cc: 'Bruce Momjian'; 'admin(at)postgresql(dot)org'; 'pgsql-sql(at)postgresql(dot)org'
> > 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. #
> > #================================================== JanWieck(at)Yahoo(dot)com #
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck(at)Yahoo(dot)com #
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026