Re: [SQL] how to write procedures
Hi, it seems you weren't yet installed appropriate procedural language. for example, if you wish to code in plpgsql you must install 'plpgsql'. for details refer the documentation and it has enough information. regards, bhuvaneswaran. On Thu, 4 Jul 2002, srikanth wrote: > Hi, I am using postgre sql server on linux server but for my database I am > using storedprocedures which i need to create , but there are no commands to > create procedures it says it does not support is there any way to work with > stored procedures in postgre sql server. > thanks, > srikanth. > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL]
jack wrote: > Hi, > Regarding temp tables in the same connection session. If there is SQL > procedure creating temp tables, process temp tables and drop all the temp > tables finally. This SQL procedure can't run twice in the same session. > Because all the temp tables are referred to the first physical temp tables. > I posted this question couple months ago and the final reply is that in the > future version, all the sql procedures, which use temp tables, will be > forced to re-compile though it's been used in the session. > > Now, my question is, if it has been done, do I still need to drop all the > temp tables before I re-run the same SQL procedure in the same session? Actually, we recomment using EXECUTE for any temp table access from pl/pgsql stored procedures. We even have an FAQ item now: 4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL functions? PL/PgSQL caches function contents, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] export plpgsql function to file
how can i export my postgresql function to a file? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SQL problem with aggregate functions.
Hi the list ! I've got a table in which there is a field that can have one amongst 3 possible values : D, R, X. Is it possible to get in one query the count of this different values.Please, note that I don't want to have a querry like this : "select count (*) from tab group by f1;", cause i want to get all the possible count values in one row (these data are already grouped on another field). To give a more accurate example, here is what I want to retrieve : Field group | count of D | count of R | count of X. Any clues ? -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Variables in PSQL
Hi I'm trying to declare a variable in PostgreSQL, so I can save some values in it. After, I want to calculate with this variable. For example: declare vp integer; select price into :vp from article where anr = 1; vp := vp + 1; update article set price = :vp where anr = 1; Is there a posibility to do that without creating a funktion? Thanks for your answers. CU Roger ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Rule WHERE condition problem
"Kristian Eide" <[EMAIL PROTECTED]> writes: > ERROR: query rewritten 10 times, may contain cycles > It would seem that my WHERE clause is not checked before the action is run. No, the WHERE clause is essentially transformed into part of the rule query. You can't ever write a rule of the form ON UPDATE TO b DO UPDATE b ... because it *will* be an infinite loop, condition or no condition. Consider using a trigger instead. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] PLPGSQL language documentation
I was wondering if anyone knew of good online documentation on PlPgsql? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rule WHERE condition problem
>> ERROR: query rewritten 10 times, may contain cycles >> It would seem that my WHERE clause is not checked before the action is run. >No, the WHERE clause is essentially transformed into part of the rule >query. You can't ever write a rule of the form > ON UPDATE TO b DO UPDATE b ... >because it *will* be an infinite loop, condition or no condition. OK, perhaps the documentation should state that recursive rules are not supported. >Consider using a trigger instead. Yes, I have written a PL/pgsql function and a trigger for this, which seems to work perfectly. A rule would have been much simpler though :) Thanks. --- Kristian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] is there a way to get hh:mm:ss given seconds
Sir, Is there a way in sql such that if I give seconds it should me return me hours:mins:seconds Eg. Seconds hh:mm:ss 422 1:01:02 eagerly waiting for response. Regards Narendra DISCLAIMER: This email is bound by the terms and conditions described at http://www.subexgroup.com/mail-disclaimer.htm
Re: [SQL] newbie question
On Sun, 7 Jul 2002, Mirco D'Angelo wrote: > Hi > > I am going to learn MySql 'cause I have to, but would it be better, or let's > say, more interesting, to learn postgressql? Is it newer, more common, etc.? > Well IMO you just cant compare them. Think of a porche that does 100km/h better than a Mirage F1 military aircraft, but F1 can do much higher accelerations at higher speeds, move in 3D space rather than 2D, etc... If you wanna travel fast on the ground go with the porche. If you want the skies go with F1!! > greets > mirco > > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] newbie question
> Transactions (well, recently mysql allows them but using propietary > extensions), foreign key relationships, subqueries, stored > procedures/triggers. MySQL lacks all of these. MySQL's InnoDB tables have foreign keys, but they are RESTRICT only - ie. they're kinda useless. > On the other side, postgres is only ported on *nix platforms, but you > can put cygwin/cygipc on Windows, if that is the case. And mysql is > "friendlier", that means wrong column value types (INT_VALUE = '5') and > double quotes working as simple quotes, so it's easier to start with. It's a trap that 'easy to use' factor. Problem with MySQL is that they just make up whole swathes of their SQL syntax out of whole cloth. They just make it up. Then, when you go to use any other SQL-standard database on Earth you have a rather painful learning and code conversion process. Just talk to anyone who uses MySQL's date and time functions, ISNULL(field), or 'KEY's and stuff... Poor newbies get to thinking that MySQL's way is the standard way, but it's not. That's what happened to me, and my life has been a wonderful thing ever since we ditched MySQL in favour of Postgres in our company! Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Rule WHERE condition problem
Kristian Eide wrote: > > I have a table with a reference constraint and an ON DELETE SET NULL action. > When this action triggers, I also want to update another field in the table, > actually a timestamp which should be set to NOW(). > > After reading some documentation it would seem a rule is the easiest way to > accomplish this. However, I ran into a problem with this: > > CREATE TABLE a ( > id INT PRIMARY KEY > ); > CREATE TABLE b ( > id INT REFERENCES a ON DELETE SET NULL, > time TIMESTAMP DEFAULT 'infinity' > ); > INSERT INTO a VALUES (1); > INSERT INTO b VALUES (1); > > CREATE RULE b_id_null AS ON UPDATE TO b WHERE new.time='infinity' AND old.id > IS NOT NULL AND new.id IS NULL DO UPDATE b SET time=NOW() where id=old.id; > > DELETE FROM a WHERE id=1; > > I would now expect a to by empty and b to contain a single row with id=NULL > and time=NOW(). However, this is what I get: > > ERROR: query rewritten 10 times, may contain cycles > ERROR: query rewritten 10 times, may contain cycles > > It would seem that my WHERE clause is not checked before the action is run. > Is this simply not implemented (yet, hopefully)? The rule system does not evaluate anything before beeing done with the rewriting. All it is looking at is "is this an UPDATE for b?. Since the rewritten result is one again, it tries to apply the same rule to that now, and again, and again. This is not a bug. Without this recursive behaviour, views of views would not be possible. You can implement the functionality you want with a custom trigger. Jan -- #==# # 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] INSERT only under certain conditions (SELECT)
On Mon, Jul 08, 2002 at 17:49:56 +0200, Joachim Trinkwitz <[EMAIL PROTECTED]> wrote: > > Now I want to allow a user with id 123 only to insert a row in T if he > doesn't choose values from L with same 'art' and 'semester' values -- > in the examples user 123 has already chosen a kvvnr from semester > 2002ws in category 'art', so he shouldn't be allowed to insert the > values of the second row in table L. In the database you enforce this using a unique index. The application needs to be able to handle failures when you try to insert a duplicate key. If you can't afford to have a transaction fail part way through, then anything that writes that table should do a table lock at the start of the transaction. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] is there a way to get hh:mm:ss given seconds
On Mon, 2002-07-08 at 09:28, Narendra A wrote: > Sir, > Is there a way in sql such that if I give seconds it should me return me > hours:mins:seconds > > Eg. Seconds hh:mm:ss > 422 1:01:02 scratch=# SELECT 422::interval; interval -- 00:07:02 (1 row) scratch=# \q dstanawa@ciderbox:~$ bc -l 7*60+2 422 I don't know where you got 1:01:02 from. -- David Stanaway signature.asc Description: This is a digitally signed message part
Re: [SQL] is there a way to get hh:mm:ss given seconds
On Mon, 8 Jul 2002, Narendra A wrote: > Is there a way in sql such that if I give seconds it should me return me > hours:mins:seconds > > Eg. Seconds hh:mm:ss > 422 1:01:02 foo=# select '422 seconds'::interval; interval -- 00:07:02 (1 row) -- Tod McQuillin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PLPGSQL language documentation
Sandy, > I was wondering if anyone knew of good online documentation on PlPgsql? Read two things: 1. the online docs for PostgreSQL 7.2 under "Programmer's guide: Procedural Languages" 2. Roberto' Mello's enhanced documentation, accessable through techdocs.postgresql.org. Roberto also has a PLpgSQL function library that should give you some good examples. However, this is entirely for Postgres 7.0 and 7.1, so there are some syntax changes in the current versions -- mostly workarounds that are no longer necessary. Also, I found the first 4 chapters of O'reilly's book on PL/SQL programming to be invaluable in helping me develop an organized approach toward manageing a database using functions. However, the rest of the book is Oracle stuff not supported by Postgres, so you decide if it's worth the $50. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PLPGSQL language documentation
Hear hear! We have been looking for it as well! We have avoided using triggers to date because we do not know how to program in the language! Sandy Ear wrote: > I was wondering if anyone knew of good online documentation on PlPgsql? > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] export plpgsql function to file
On 9 Jul 2002, Joseph Syjuco wrote: select prosrc from pg_proc where proname='foofunc'; The other way is dumping the database and extracting afterwards only the create statement of your function > how can i export my postgresql function to a file? > > > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Waiting for Update
Could it be true?? I've a table with < 46000 rows. And a little Update like UPDATE foo set xxx = 'X'; needs about 15 seconds??? What's wrong? Greetings Juergen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PLPGSQL language documentation
I'll second the suggestion about looking at Oracle PL/SQL materials. I used an introductory PL/SQL text when learning about PLpgSQL, and found it very useful. The syntax/functionality is highly similar (by design, apparently). Much of the Oracle PL/SQL functionality is not (yet) implemented in PLpgSQL however, so better to avoid detailed Oracle texts. Skip over the stuff about explicit cursors and exception handlers. --- Josh Berkus <[EMAIL PROTECTED]> wrote: > Sandy, > > > I was wondering if anyone knew of good online > documentation on PlPgsql? > > Read two things: > > 1. the online docs for PostgreSQL 7.2 under > "Programmer's guide: Procedural > Languages" > > 2. Roberto' Mello's enhanced documentation, > accessable through > techdocs.postgresql.org. Roberto also has a PLpgSQL > function library that > should give you some good examples. However, this > is entirely for Postgres > 7.0 and 7.1, so there are some syntax changes in the > current versions -- > mostly workarounds that are no longer necessary. > > Also, I found the first 4 chapters of O'reilly's > book on PL/SQL programming to > be invaluable in helping me develop an organized > approach toward manageing a > database using functions. However, the rest of the > book is Oracle stuff not > supported by Postgres, so you decide if it's worth > the $50. > > -- > -Josh Berkus > > __AGLIO DATABASE > SOLUTIONS___ > Josh Berkus >Complete information technology > [EMAIL PROTECTED] > and data management solutions (415) 565-7293 >for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] is there a way to get hh:mm:ss given seconds
Tod McQuillin wrote: > On Mon, 8 Jul 2002, Narendra A wrote: > > >>Is there a way in sql such that if I give seconds it should me return me >>hours:mins:seconds >> >>Eg. Seconds hh:mm:ss >>422 1:01:02 > > > foo=# select '422 seconds'::interval; > interval > -- > 00:07:02 > (1 row) That works on inserts into a "time" field too, cool tip. THANKS! Kevin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] 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:[EMAIL PROTECTED]]
Sent: Wednesday, July 03, 2002 12:03 PM
To: 'Jan Wieck'; Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
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:[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?
Re: [SQL] 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pg_restore cannot restore index
Yep, documentation is wrong. Documentation patch attached and applied.
Also, in 7.3 you will not need the weird quoting for objects.
---
Jie Liang wrote:
> 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:[EMAIL PROTECTED]]
> Sent: Wednesday, July 03, 2002 12:03 PM
> To: 'Jan Wieck'; Jie Liang
> Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> 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:[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 br
Re: [SQL] is there a way to get hh:mm:ss given seconds
Narendra, > I tried all these. This is what I get > SQL> SELECT "interval"(cast(422 as varchar) || ' seconds'); > SELECT "interval"(cast(422 as varchar) || ' seconds') > * > ERROR at line 1: > ORA-00906: missing left parenthesis > SQL> select '422 seconds'::interval; > select '422 seconds'::interval > * > ERROR at line 1: > ORA-00923: FROM keyword not found where expected Those don't look like PostgreSQL error messages. I think you are using some other database. -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] is there a way to get hh:mm:ss given seconds
> > I tried all these. This is what I get > > SQL> SELECT "interval"(cast(422 as varchar) || ' seconds'); > > SELECT "interval"(cast(422 as varchar) || ' seconds') > > * > > ERROR at line 1: > > ORA-00906: missing left parenthesis > > > SQL> select '422 seconds'::interval; > > select '422 seconds'::interval > > * > > ERROR at line 1: > > ORA-00923: FROM keyword not found where expected > > Those don't look like PostgreSQL error messages. I think you are using > some other database. Yes - he's using Oracle. I think Narendra is rather confused... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
