pg_restore cannot restore index

Another possible bug:
pg_restore -i "\"indexname\"" -d mydb mydumpfile
msg:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION "plpgsql_call_handler" ()
pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error

I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG
struct option cmdopts[] = {
{"clean", 0, NULL, 'c'},
{"create", 0, NULL, 'C'},
{"data-only", 0, NULL, 'a'},
{"dbname", 1, NULL, 'd'},
{"file", 1, NULL, 'f'},
{"format", 1, NULL, 'F'},
{"function", 1, NULL, 'P'},
{"host", 1, NULL, 'h'},
{"ignore-version", 0, NULL, 'i'},
{"index", 1, NULL, 'I'},
So, -i may be mapped wrong, however, -I is illegal option.

Thanks!

Jie Liang

-----Original Message-----
From: Jie Liang [mailto:jie(at)stbernard(dot)com]
Sent: Wednesday, July 03, 2002 12:03 PM
To: 'Jan Wieck'; Jie Liang
Cc: 'Bruce Momjian'; 'admin(at)postgresql(dot)org'; 'pgsql-sql(at)postgresql(dot)org'
Subject: Re: [SQL] pg_restore cannot restore function

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 #

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org