pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore cannot restore function
13+ messages / 5 participants
[nested] [flat]
* pg_restore cannot restore function
@ 2002-06-26 23:55 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jie Liang @ 2002-06-26 23:55 UTC (permalink / raw)
To: 'admin@postgresql.org' <admin@postgresql.org>; +Cc: pgsql-sql
I use
pg_dump -Fc mydb > dbf
then I create anpther db by:
createdb mydb2
I use
pg_restore -P myfunction -d mydb2 dbf
cannot restore myfunction into mydb2
why??????
Jie Liang
^ permalink raw reply [nested|flat] 13+ messages in thread
* pg_restore cannot restore function
@ 2002-06-28 18:49 Jie Liang <jie@stbernard.com>
2002-06-28 19:39 ` Re: pg_restore cannot restore function Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Jie Liang @ 2002-06-28 18:49 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; +Cc: pgsql-sql
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??????
Jie Liang
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
2002-06-28 18:49 pg_restore cannot restore function Jie Liang <jie@stbernard.com>
@ 2002-06-28 19:39 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jan Wieck @ 2002-06-28 19:39 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
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@Yahoo.com #
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
@ 2002-06-28 20:46 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jie Liang @ 2002-06-28 20:46 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
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@Yahoo.com]
Sent: Friday, June 28, 2002 12:39 PM
To: Jie Liang
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com #
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
@ 2002-06-28 20:49 Jie Liang <jie@stbernard.com>
2002-07-01 18:13 ` Re: pg_restore cannot restore function Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Jie Liang @ 2002-06-28 20:49 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; Jan Wieck <JanWieck@Yahoo.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
Oops,my OS is FreeBSD4.3 PostgreSQL7.2
Thanks
Jie Liang
-----Original Message-----
From: Jie Liang
Sent: Friday, June 28, 2002 1:46 PM
To: 'Jan Wieck'
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com]
Sent: Friday, June 28, 2002 12:39 PM
To: Jie Liang
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com #
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
2002-06-28 20:49 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
@ 2002-07-01 18:13 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jan Wieck @ 2002-07-01 18:13 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
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@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com]
> Sent: Friday, June 28, 2002 12:39 PM
> To: Jie Liang
> Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com #
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
@ 2002-07-02 23:08 Jie Liang <jie@stbernard.com>
2002-07-03 07:51 ` Re: pg_restore cannot restore function Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Jie Liang @ 2002-07-02 23:08 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
I am sure.
I assume that
pg_restore -t mytable -d mydb mydumpfile
is same as
pg_restore --table=mytable --dbname=mydb mydumpfile
but it is not!
the 2nd one will get:
pg_restore: [archiver] could not open input file: No such file or directory
Jie Liang
-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Monday, July 01, 2002 11:14 AM
To: Jie Liang
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com]
> Sent: Friday, June 28, 2002 12:39 PM
> To: Jie Liang
> Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com #
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
2002-07-02 23:08 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
@ 2002-07-03 07:51 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 13+ messages in thread
From: Achilleus Mantzios @ 2002-07-03 07:51 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: 'Jan Wieck' <JanWieck@Yahoo.com>; 'Bruce Momjian' <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
In the case that you moved your backup to another system
where possibly the shared library (.so) where the function exists is on a
different location then thats the problem, in which case you only need
to recreate the function (with the same isstrict,iscachable attributes).
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
@ 2002-07-03 15:25 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jie Liang @ 2002-07-03 15:25 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Jie Liang <jie@stbernard.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
This is not the case, because those db on a same server, it's
I dump data from one db and try restore one of it function into
another db.
Thanks for your response anyway.
Jie Liang
-----Original Message-----
From: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com]
Sent: Wednesday, July 03, 2002 12:52 AM
To: Jie Liang
Cc: 'Jan Wieck'; 'Bruce Momjian'; 'admin@postgresql.org';
'pgsql-sql@postgresql.org'
Subject: Re: [SQL] pg_restore cannot restore function
In the case that you moved your backup to another system
where possibly the shared library (.so) where the function exists is on a
different location then thats the problem, in which case you only need
to recreate the function (with the same isstrict,iscachable attributes).
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_restore cannot restore function
@ 2002-07-03 19:03 Jie Liang <jie@stbernard.com>
2002-07-04 03:03 ` Re: [SQL] pg_restore cannot restore function Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Jie Liang @ 2002-07-03 19:03 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; Jie Liang <jie@stbernard.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; 'admin@postgresql.org' <admin@postgresql.org>; pgsql-sql
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@Yahoo.com]
Sent: Monday, July 01, 2002 11:14 AM
To: Jie Liang
Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com]
> Sent: Friday, June 28, 2002 12:39 PM
> To: Jie Liang
> Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com #
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] pg_restore cannot restore function
2002-07-03 19:03 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
@ 2002-07-04 03:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-04 05:33 ` Re: [SQL] pg_restore cannot restore function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2002-07-04 03:03 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: PostgreSQL-patches <pgsql-patches@postgresql.org>
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@Yahoo.com]
> Sent: Monday, July 01, 2002 11:14 AM
> To: Jie Liang
> Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com]
> > Sent: Friday, June 28, 2002 12:39 PM
> > To: Jie Liang
> > Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.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@Yahoo.com #
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck@Yahoo.com #
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Attachments:
/bjm/diff (text/plain, 20.8K) download
Index: doc/src/sgml/ref/pg_restore.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_restore.sgml,v
retrieving revision 1.25
diff -c -r1.25 pg_restore.sgml
*** doc/src/sgml/ref/pg_restore.sgml 10 May 2002 22:36:26 -0000 1.25
--- doc/src/sgml/ref/pg_restore.sgml 4 Jul 2002 02:56:19 -0000
***************
*** 33,39 ****
<arg> -L <replaceable class="parameter">contents-file</replaceable> </arg>
<group> <arg> -N </arg> <arg> -o </arg> <arg> -r </arg> </group>
<arg> -O </arg>
! <arg> -P <replaceable class="parameter">function-name</replaceable> </arg>
<arg> -R </arg>
<arg> -s </arg>
<arg> -S </arg>
--- 33,39 ----
<arg> -L <replaceable class="parameter">contents-file</replaceable> </arg>
<group> <arg> -N </arg> <arg> -o </arg> <arg> -r </arg> </group>
<arg> -O </arg>
! <arg> -P <replaceable class="parameter">function-name(argtype [, ...])</replaceable> </arg>
<arg> -R </arg>
<arg> -s </arg>
<arg> -S </arg>
***************
*** 276,283 ****
</varlistentry>
<varlistentry>
! <term><option>-P <replaceable class="parameter">function-name</replaceable></option></term>
! <term><option>--function=<replaceable class="parameter">function-name</replaceable></option></term>
<listitem>
<para>
Specify a procedure or function to be restored.
--- 276,283 ----
</varlistentry>
<varlistentry>
! <term><option>-P <replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term>
! <term><option>--function=<replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term>
<listitem>
<para>
Specify a procedure or function to be restored.
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.50
diff -c -r1.50 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c 2 Jul 2002 05:49:51 -0000 1.50
--- src/bin/pg_dump/pg_backup_archiver.c 4 Jul 2002 02:56:21 -0000
***************
*** 191,196 ****
--- 191,197 ----
* initially connected to, not the one we will create, which is very
* bad...
*/
+
if (ropt->create && ropt->noReconnect)
die_horribly(AH, modulename, "-C and -R are incompatible options\n");
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.268
diff -c -r1.268 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 2 Jul 2002 05:49:51 -0000 1.268
--- src/bin/pg_dump/pg_dump.c 4 Jul 2002 02:56:24 -0000
***************
*** 104,117 ****
static void dumpFuncACL(Archive *fout, FuncInfo *finfo);
static void dumpAggACL(Archive *fout, AggInfo *finfo);
static void dumpACL(Archive *fout, const char *type, const char *name,
! const char *nspname, const char *usename,
! const char *acl, const char *objoid);
static void dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables);
static void dumpRules(Archive *fout, TableInfo *tblinfo, int numTables);
static void formatStringLiteral(PQExpBuffer buf, const char *str,
const formatLiteralOptions opts);
! static char *format_function_signature(FuncInfo *finfo);
static void dumpOneFunc(Archive *fout, FuncInfo *finfo);
static void dumpOneOpr(Archive *fout, OprInfo *oprinfo,
OprInfo *g_oprinfo, int numOperators);
--- 104,117 ----
static void dumpFuncACL(Archive *fout, FuncInfo *finfo);
static void dumpAggACL(Archive *fout, AggInfo *finfo);
static void dumpACL(Archive *fout, const char *type, const char *name,
! const char *name_noquotes, const char *nspname,
! const char *usename, const char *acl, const char *objoid);
static void dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables);
static void dumpRules(Archive *fout, TableInfo *tblinfo, int numTables);
static void formatStringLiteral(PQExpBuffer buf, const char *str,
const formatLiteralOptions opts);
! static char *format_function_signature(FuncInfo *finfo, bool honor_quotes);
static void dumpOneFunc(Archive *fout, FuncInfo *finfo);
static void dumpOneOpr(Archive *fout, OprInfo *oprinfo,
OprInfo *g_oprinfo, int numOperators);
***************
*** 2721,2727 ****
if (strcmp(nspinfo->nspname, "public") == 0)
{
if (!aclsSkip && strcmp(nspinfo->nspacl, "{=UC}") != 0)
! dumpACL(fout, "SCHEMA", qnspname, NULL,
nspinfo->usename, nspinfo->nspacl,
nspinfo->oid);
}
--- 2721,2727 ----
if (strcmp(nspinfo->nspname, "public") == 0)
{
if (!aclsSkip && strcmp(nspinfo->nspacl, "{=UC}") != 0)
! dumpACL(fout, "SCHEMA", qnspname, nspinfo->nspname, NULL,
nspinfo->usename, nspinfo->nspacl,
nspinfo->oid);
}
***************
*** 2747,2753 ****
nspinfo->oid, "pg_namespace", 0, NULL);
if (!aclsSkip)
! dumpACL(fout, "SCHEMA", qnspname, NULL,
nspinfo->usename, nspinfo->nspacl,
nspinfo->oid);
}
--- 2747,2753 ----
nspinfo->oid, "pg_namespace", 0, NULL);
if (!aclsSkip)
! dumpACL(fout, "SCHEMA", qnspname, nspinfo->nspname, NULL,
nspinfo->usename, nspinfo->nspacl,
nspinfo->oid);
}
***************
*** 3291,3298 ****
if (!aclsSkip)
{
! char * tmp = strdup(fmtId(lanname, force_quotes));
! dumpACL(fout, "LANGUAGE", tmp, finfo[fidx].pronamespace->nspname,
NULL, lanacl, lanoid);
free(tmp);
}
--- 3291,3299 ----
if (!aclsSkip)
{
! char *tmp = strdup(fmtId(lanname, force_quotes));
! dumpACL(fout, "LANGUAGE", tmp, lanname,
! finfo[fidx].pronamespace->nspname,
NULL, lanacl, lanoid);
free(tmp);
}
***************
*** 3333,3345 ****
* is never qualified.
*/
static char *
! format_function_signature(FuncInfo *finfo)
{
PQExpBufferData fn;
int j;
initPQExpBuffer(&fn);
! appendPQExpBuffer(&fn, "%s (", fmtId(finfo->proname, force_quotes));
for (j = 0; j < finfo->nargs; j++)
{
char *typname;
--- 3334,3349 ----
* is never qualified.
*/
static char *
! format_function_signature(FuncInfo *finfo, bool honor_quotes)
{
PQExpBufferData fn;
int j;
initPQExpBuffer(&fn);
! if (honor_quotes)
! appendPQExpBuffer(&fn, "%s(", fmtId(finfo->proname, force_quotes));
! else
! appendPQExpBuffer(&fn, "%s(", finfo->proname);
for (j = 0; j < finfo->nargs; j++)
{
char *typname;
***************
*** 3358,3369 ****
static void
dumpFuncACL(Archive *fout, FuncInfo *finfo)
{
! char *funcsig;
! funcsig = format_function_signature(finfo);
! dumpACL(fout, "FUNCTION", funcsig, finfo->pronamespace->nspname,
finfo->usename, finfo->proacl, finfo->oid);
free(funcsig);
}
--- 3362,3376 ----
static void
dumpFuncACL(Archive *fout, FuncInfo *finfo)
{
! char *funcsig, *funcsig_noquotes;
! funcsig = format_function_signature(finfo, true);
! funcsig_noquotes = format_function_signature(finfo, false);
! dumpACL(fout, "FUNCTION", funcsig, funcsig_noquotes,
! finfo->pronamespace->nspname,
finfo->usename, finfo->proacl, finfo->oid);
free(funcsig);
+ free(funcsig_noquotes);
}
***************
*** 3380,3385 ****
--- 3387,3393 ----
PQExpBuffer asPart = createPQExpBuffer();
PGresult *res = NULL;
char *funcsig = NULL;
+ char *funcsig_noquotes = NULL;
int ntups;
char *proretset;
char *prosrc;
***************
*** 3487,3493 ****
}
}
! funcsig = format_function_signature(finfo);
/* DROP must be fully qualified in case same name appears in pg_catalog */
appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
--- 3495,3502 ----
}
}
! funcsig = format_function_signature(finfo, true);
! funcsig_noquotes = format_function_signature(finfo, false);
/* DROP must be fully qualified in case same name appears in pg_catalog */
appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
***************
*** 3517,3523 ****
finfo->proname);
exit_nicely();
}
! }
if (proimplicit[0] == 't')
appendPQExpBuffer(q, " IMPLICIT CAST");
--- 3526,3532 ----
finfo->proname);
exit_nicely();
}
! }
if (proimplicit[0] == 't')
appendPQExpBuffer(q, " IMPLICIT CAST");
***************
*** 3530,3536 ****
appendPQExpBuffer(q, ";\n");
! ArchiveEntry(fout, finfo->oid, funcsig, finfo->pronamespace->nspname,
finfo->usename, "FUNCTION", NULL,
q->data, delqry->data,
NULL, NULL, NULL);
--- 3539,3546 ----
appendPQExpBuffer(q, ";\n");
! ArchiveEntry(fout, finfo->oid, funcsig_noquotes,
! finfo->pronamespace->nspname,
finfo->usename, "FUNCTION", NULL,
q->data, delqry->data,
NULL, NULL, NULL);
***************
*** 3551,3556 ****
--- 3561,3567 ----
destroyPQExpBuffer(delqry);
destroyPQExpBuffer(asPart);
free(funcsig);
+ free(funcsig_noquotes);
}
/*
***************
*** 3939,3952 ****
* is never qualified.
*/
static char *
! format_aggregate_signature(AggInfo *agginfo, Archive *fout)
{
PQExpBufferData buf;
bool anybasetype;
initPQExpBuffer(&buf);
! appendPQExpBuffer(&buf, "%s",
fmtId(agginfo->aggname, force_quotes));
anybasetype = (strcmp(agginfo->aggbasetype, "0") == 0);
--- 3950,3966 ----
* is never qualified.
*/
static char *
! format_aggregate_signature(AggInfo *agginfo, Archive *fout, bool honor_quotes)
{
PQExpBufferData buf;
bool anybasetype;
initPQExpBuffer(&buf);
! if (honor_quotes)
! appendPQExpBuffer(&buf, "%s",
fmtId(agginfo->aggname, force_quotes));
+ else
+ appendPQExpBuffer(&buf, "%s", agginfo->aggname);
anybasetype = (strcmp(agginfo->aggbasetype, "0") == 0);
***************
*** 3974,3985 ****
static void
dumpAggACL(Archive *fout, AggInfo *finfo)
{
! char *aggsig;
! aggsig = format_aggregate_signature(finfo, fout);
! dumpACL(fout, "FUNCTION", aggsig, finfo->aggnamespace->nspname,
finfo->usename, finfo->aggacl, finfo->oid);
free(aggsig);
}
--- 3988,4002 ----
static void
dumpAggACL(Archive *fout, AggInfo *finfo)
{
! char *aggsig, *aggsig_noquotes;
! aggsig = format_aggregate_signature(finfo, fout, true);
! aggsig_noquotes = format_aggregate_signature(finfo, fout, false);
! dumpACL(fout, "FUNCTION", aggsig, aggsig_noquotes,
! finfo->aggnamespace->nspname,
finfo->usename, finfo->aggacl, finfo->oid);
free(aggsig);
+ free(aggsig_noquotes);
}
***************
*** 3994,4000 ****
PQExpBuffer q = createPQExpBuffer();
PQExpBuffer delq = createPQExpBuffer();
PQExpBuffer details = createPQExpBuffer();
! char *aggSig;
PGresult *res;
int ntups;
int i_aggtransfn;
--- 4011,4018 ----
PQExpBuffer q = createPQExpBuffer();
PQExpBuffer delq = createPQExpBuffer();
PQExpBuffer details = createPQExpBuffer();
! char *aggsig;
! char *aggsig_noquotes;
PGresult *res;
int ntups;
int i_aggtransfn;
***************
*** 4084,4099 ****
agginfo->fmtbasetype = strdup(PQgetvalue(res, 0, i_fmtbasetype));
convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
! aggSig = format_aggregate_signature(agginfo, g_fout);
if (!convertok)
{
write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
! aggSig);
appendPQExpBuffer(q, "-- WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
! aggSig);
! ArchiveEntry(fout, agginfo->oid, aggSig,
agginfo->aggnamespace->nspname, agginfo->usename,
"WARNING", NULL,
q->data, "" /* Del */ ,
--- 4102,4118 ----
agginfo->fmtbasetype = strdup(PQgetvalue(res, 0, i_fmtbasetype));
convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
! aggsig = format_aggregate_signature(agginfo, g_fout, true);
! aggsig_noquotes = format_aggregate_signature(agginfo, g_fout, false);
if (!convertok)
{
write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
! aggsig);
appendPQExpBuffer(q, "-- WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
! aggsig);
! ArchiveEntry(fout, agginfo->oid, aggsig_noquotes,
agginfo->aggnamespace->nspname, agginfo->usename,
"WARNING", NULL,
q->data, "" /* Del */ ,
***************
*** 4146,4158 ****
/* DROP must be fully qualified in case same name appears in pg_catalog */
appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
fmtId(agginfo->aggnamespace->nspname, force_quotes),
! aggSig);
appendPQExpBuffer(q, "CREATE AGGREGATE %s ( %s );\n",
fmtId(agginfo->aggname, force_quotes),
details->data);
! ArchiveEntry(fout, agginfo->oid, aggSig,
agginfo->aggnamespace->nspname, agginfo->usename,
"AGGREGATE", NULL,
q->data, delq->data,
--- 4165,4177 ----
/* DROP must be fully qualified in case same name appears in pg_catalog */
appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
fmtId(agginfo->aggnamespace->nspname, force_quotes),
! aggsig);
appendPQExpBuffer(q, "CREATE AGGREGATE %s ( %s );\n",
fmtId(agginfo->aggname, force_quotes),
details->data);
! ArchiveEntry(fout, agginfo->oid, aggsig_noquotes,
agginfo->aggnamespace->nspname, agginfo->usename,
"AGGREGATE", NULL,
q->data, delq->data,
***************
*** 4161,4167 ****
/*** Dump Aggregate Comments ***/
resetPQExpBuffer(q);
! appendPQExpBuffer(q, "AGGREGATE %s", aggSig);
if (g_fout->remoteVersion >= 70300)
dumpComment(fout, q->data,
agginfo->aggnamespace->nspname, agginfo->usename,
--- 4180,4186 ----
/*** Dump Aggregate Comments ***/
resetPQExpBuffer(q);
! appendPQExpBuffer(q, "AGGREGATE %s", aggsig);
if (g_fout->remoteVersion >= 70300)
dumpComment(fout, q->data,
agginfo->aggnamespace->nspname, agginfo->usename,
***************
*** 4177,4183 ****
destroyPQExpBuffer(q);
destroyPQExpBuffer(delq);
destroyPQExpBuffer(details);
! free(aggSig);
}
--- 4196,4203 ----
destroyPQExpBuffer(q);
destroyPQExpBuffer(delq);
destroyPQExpBuffer(details);
! free(aggsig);
! free(aggsig_noquotes);
}
***************
*** 4276,4282 ****
*/
static void
dumpACL(Archive *fout, const char *type, const char *name,
! const char *nspname, const char *usename,
const char *acls, const char *objoid)
{
char *aclbuf,
--- 4296,4302 ----
*/
static void
dumpACL(Archive *fout, const char *type, const char *name,
! const char *name_noquotes, const char *nspname, const char *usename,
const char *acls, const char *objoid)
{
char *aclbuf,
***************
*** 4390,4396 ****
appendPQExpBuffer(sql, "%s;\n", fmtId(usename, force_quotes));
}
! ArchiveEntry(fout, objoid, name, nspname, usename ? usename : "",
"ACL", NULL, sql->data, "", NULL, NULL, NULL);
free(aclbuf);
--- 4410,4416 ----
appendPQExpBuffer(sql, "%s;\n", fmtId(usename, force_quotes));
}
! ArchiveEntry(fout, objoid, name_noquotes, nspname, usename ? usename : "",
"ACL", NULL, sql->data, "", NULL, NULL, NULL);
free(aclbuf);
***************
*** 4401,4409 ****
static void
dumpTableACL(Archive *fout, TableInfo *tbinfo)
{
! char * tmp = strdup( fmtId(tbinfo->relname, force_quotes) );
! dumpACL(fout, "TABLE", tmp, tbinfo->relnamespace->nspname,
! tbinfo->usename, tbinfo->relacl,
tbinfo->viewoid != NULL ? tbinfo->viewoid : tbinfo->oid);
free(tmp);
}
--- 4421,4429 ----
static void
dumpTableACL(Archive *fout, TableInfo *tbinfo)
{
! char *tmp = strdup(fmtId(tbinfo->relname, force_quotes));
! dumpACL(fout, "TABLE", tmp, tbinfo->relname,
! tbinfo->relnamespace->nspname, tbinfo->usename, tbinfo->relacl,
tbinfo->viewoid != NULL ? tbinfo->viewoid : tbinfo->oid);
free(tmp);
}
***************
*** 5793,5812 ****
precision, scale);
}
}
-
/*
* char is an internal single-byte data type; Let's make sure we force
* it through with quotes. - thomas 1998-12-13
*/
else if (!strcmp(typname, "char"))
{
! appendPQExpBuffer(buf, "%s",
! fmtId(typname, true));
}
else
{
! appendPQExpBuffer(buf, "%s",
! fmtId(typname, false));
}
result = strdup(buf->data);
--- 5813,5829 ----
precision, scale);
}
}
/*
* char is an internal single-byte data type; Let's make sure we force
* it through with quotes. - thomas 1998-12-13
*/
else if (!strcmp(typname, "char"))
{
! appendPQExpBuffer(buf, "%s", fmtId(typname, true));
}
else
{
! appendPQExpBuffer(buf, "%s", fmtId(typname, false));
}
result = strdup(buf->data);
Index: src/bin/pg_dump/pg_restore.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_restore.c,v
retrieving revision 1.34
diff -c -r1.34 pg_restore.c
*** src/bin/pg_dump/pg_restore.c 10 May 2002 22:36:27 -0000 1.34
--- src/bin/pg_dump/pg_restore.c 4 Jul 2002 02:56:25 -0000
***************
*** 65,70 ****
--- 65,71 ----
/* Forward decls */
static void usage(const char *progname);
static char *_cleanupName(char *name);
+ static char *_cleanupFuncName(char *name);
typedef struct option optType;
***************
*** 220,226 ****
case 'P': /* Function */
opts->selTypes = 1;
opts->selFunction = 1;
! opts->functionNames = optarg ? strdup(optarg) : NULL;
break;
case 'I': /* Index */
opts->selTypes = 1;
--- 221,227 ----
case 'P': /* Function */
opts->selTypes = 1;
opts->selFunction = 1;
! opts->functionNames = _cleanupFuncName(optarg);
break;
case 'I': /* Index */
opts->selTypes = 1;
***************
*** 392,398 ****
" -O, --no-owner do not reconnect to database to match\n"
" object owner\n"
" -p, --port=PORT server port number\n"
! " -P, --function=NAME restore named function\n"
" -r, --rearrange rearrange output to put indexes etc. at end\n"
" -R, --no-reconnect disallow ALL reconnections to the database\n"
" -s, --schema-only restore only the schema, no data\n"
--- 393,399 ----
" -O, --no-owner do not reconnect to database to match\n"
" object owner\n"
" -p, --port=PORT server port number\n"
! " -P, --function=NAME(args)restore named function\n"
" -r, --rearrange rearrange output to put indexes etc. at end\n"
" -R, --no-reconnect disallow ALL reconnections to the database\n"
" -s, --schema-only restore only the schema, no data\n"
***************
*** 430,436 ****
" -O do not reconnect to database to match\n"
" object owner\n"
" -p PORT server port number\n"
! " -P NAME restore named function\n"
" -r rearrange output to put indexes etc. at end\n"
" -R disallow ALL reconnections to the database\n"
" -s restore only the schema, no data\n"
--- 431,437 ----
" -O do not reconnect to database to match\n"
" object owner\n"
" -p PORT server port number\n"
! " -P NAME(args) restore named function\n"
" -r rearrange output to put indexes etc. at end\n"
" -R disallow ALL reconnections to the database\n"
" -s restore only the schema, no data\n"
***************
*** 476,480 ****
--- 477,529 ----
if (isupper((unsigned char) name[i]))
name[i] = tolower((unsigned char) name[i]);
}
+ return name;
+ }
+
+
+ static char *
+ _cleanupFuncName(char *name)
+ {
+ int i;
+ char *ch;
+
+ if (!name || !name[0])
+ return NULL;
+
+ name = strdup(name);
+
+ if (name[0] == '"')
+ {
+ strcpy(name, &name[1]);
+ if (strchr(name, '"') != NULL)
+ strcpy(strchr(name, '"'), strchr(name, '"')+1);
+ }
+ /* otherwise, convert function name to lowercase... */
+ else
+ {
+ for (i = 0; name[i]; i++)
+ if (isupper((unsigned char) name[i]))
+ name[i] = tolower((unsigned char) name[i]);
+ }
+
+ /* strip out any space before paren */
+ ch = strchr(name,'(');
+ while (ch && ch > name && *(ch-1) == ' ')
+ {
+ strcpy(ch - 1, ch);
+ ch--;
+ }
+
+ /*
+ * Strip out spaces after commas in parameter list.
+ * We can't remove all spaces because some types, like
+ * 'double precision' have spaces.
+ */
+ if ((ch = strchr(name,'(')) != NULL)
+ {
+ while ((ch = strstr(ch,", ")) != NULL)
+ strcpy(ch + 1, ch + 2);
+ }
+
return name;
}
* Re: [SQL] pg_restore cannot restore function
2002-07-03 19:03 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-07-04 03:03 ` Re: [SQL] pg_restore cannot restore function Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-07-04 05:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-07-04 05:36 ` Re: [SQL] pg_restore cannot restore function Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2002-07-04 05:33 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jie Liang <jie@stbernard.com>; PostgreSQL-patches <pgsql-patches@postgresql.org>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The following patch fixes all of these, and it will be in 7.3.
It will? Kindly explain what the heck this is doing. It looks to
me like it will probably break more cases than it fixes. AFAICS
it's just completely muddying the waters about what is quoted and
what isn't ...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] pg_restore cannot restore function
2002-07-03 19:03 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-07-04 03:03 ` Re: [SQL] pg_restore cannot restore function Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-04 05:33 ` Re: [SQL] pg_restore cannot restore function Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-07-04 05:36 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2002-07-04 05:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jie Liang <jie@stbernard.com>; PostgreSQL-patches <pgsql-patches@postgresql.org>
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > The following patch fixes all of these, and it will be in 7.3.
>
> It will? Kindly explain what the heck this is doing. It looks to
> me like it will probably break more cases than it fixes. AFAICS
> it's just completely muddying the waters about what is quoted and
> what isn't ...
Well, the waters were already muddy. Doing a pg_dump -Ft, you will see
with the old code how the tags for functions had quotes, and how the
GRANT/REVOKE has quotes for table names, while the CREATE TABLE tags for
these do not. This fixes that, and tries to get the -P string they
enter for pg_restore to match the tag by removing spaces.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2002-07-04 05:36 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-26 23:55 pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-06-28 18:49 pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-06-28 19:39 ` Jan Wieck <JanWieck@Yahoo.com>
2002-06-28 20:46 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-06-28 20:49 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-07-01 18:13 ` Jan Wieck <JanWieck@Yahoo.com>
2002-07-02 23:08 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-07-03 07:51 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-03 15:25 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-07-03 19:03 Re: pg_restore cannot restore function Jie Liang <jie@stbernard.com>
2002-07-04 03:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-04 05:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-07-04 05:36 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox