[SQL] query/transaction history/logs
Is there anyway we can track queries entered? Is there a table that stores all the actions that a user entered? (history) Is there such a feature? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] query/transaction history/logs
Le Mercredi 10 Juillet 2002 10:34, q a écrit : > Is there anyway we can track queries entered? > Is there a table that stores all the actions that a user entered? (history) > Is there such a feature? > For both of your problems, see the way you can launch the postmaster. For example, I use to launch it this way : postmaster -i -d2 &> /var/log/postmaster.log & Thus, you get all you want in the log file /var/log/postmaster.log Now, you can get as much infos as you want according to the level of debug you want (from 1 to 9 as far as I remember, the greater, the more speech you get from the postmaster). > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Help with function optimisation
Hi,
I've got a function that I run as a trigger to update a summary table on
insert to the main table. As you can see below, it does a select & an INSERT
if not found, or an UPDATE if found. This currently works OK, but I'd like
to improve performance by removing the SELECT & attempting an UPDATE. If it
fails, I'd like to INSERT instead. Can this be done? If so, what's the
syntax?
CREATE FUNCTION update_summary ()
RETURNS opaque AS '
DECLARE
result TEXT;
BEGIN
IF NEW.user_name NOT LIKE ''U%'' THEN
SELECT into result originator FROM summary
WHERE date = date_trunc(''hour'', NEW.logtime)
AND client_id = NEW.client_id AND originator =
NEW.originator;
IF NOT FOUND THEN
INSERT INTO summary (date, client_id, originator,
status, total)
values (date_trunc(''hour'', NEW.logtime),
NEW.client_id, NEW.originator, NEW.status, ''1'');
ELSE
UPDATE summary SET total = total + 1
WHERE date = date_trunc(''hour'', NEW.logtime)
AND client_id = NEW.client_id AND originator =
NEW.originator;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
--
Ian Cass
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem on PostgreSQL (error code, store procedures)
> > I would like to know if there are any global variables > storing the error code or the number of rows affected > after each execution of the SQL statement. You did not mention which interface you are using. In C there are functions available like extern const char *PQcmdTuples(PGresult *res); to learn about the rows affected or extern int PQntuples(PGresult *res); to learn about the number of rows found (SELECT) or extern ExecStatusType PQresultStatus(PGresult *res); to learn about the status of the SQL command. Unfortunately, there are no error codes at all. In PL/pgSQL (which I do not use) I've found GET DIAGNOSTICS variable = ROW_COUNT ; refer to plpgsql-statements.html > > Also, for Postgresql function, I have checked that I > can only call the function by: select function(parms) > or by EXECUTE PROCEDURE function(parms) in the trigger > action. I wonder whether there is any other method for > calling the function which is similar to the stored > procedures in the MS SQL server? I'm afraid NO. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Variables in PSQL
> > 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; AFAIK, you can do stuff like this only within a function. > > Is there a posibility to do that without creating a funktion? What about update article set price = (select price + 1 from article where anr = 1) where anr = 1; Regards, Christoph ---(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] Error with DISTINCT and AS keywords
Hi, I need some help to let this sql statement run with Postgres 7.2.1 Doesn't work: select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; ERROR: Unable to identify an ordering operator '<' for type 'unknown' Use an explicit ordering operator or modify the query If I remove the DISTINCT keyword it works: select tnr, titel, 'TEST' AS testcol from tTitel; Greetings, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bad SUM result
There're no another ways? It don't looks like optimal. Petr Jezek - Original Message - From: "Jean-Luc Lachance" <[EMAIL PROTECTED]> To: "Roy Souther" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, July 08, 2002 5:22 PM Subject: Re: [SQL] Bad SUM result > That is because your query is generating a cartesian product. > > Try: > > SELECT ( > SELECT SUM(totalprice) > FROM invoices > WHERE custnumber = '1' > ) - ( > SELECT SUM(paymentamount) > FROM payments > WHERE custnumber = '1' > ) > > > > Roy Souther wrote: > > > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > I have an invoice database that has two tables one for invoices and one for > > payments. I want to get the account balance for a client by subtracting the > > sum of all payments from the sum off all invoices for that client. > > > > Here is the SQL that I thought should work. > > SELECT SUM(t0.totalprice)-SUM(t1.paymentamount) FROM invoices t0, payments t1 > > WHERE t0.custnumber='1' AND t1.custnumber='1' > > > > It works fine if there is only one invoice and one payment but as soon as > > there is more then one of either it screws up. For each match found in > > payments the invoice sum is added to the total. So if client 1 purchased a > > $100 item then maid a $10 payment the SQL would return the balance of $90 > > just fine. When the client makes a second payment of $15 the balance is $75 > > but this SQL returns ($100+$100)-($10+$15) = $175. A third payment of $1 > > would return ($100+$100+$100)-($10+$15+$1) = $274. > > > > Could some one explain this to me and recommend an SQL command that would work > > please? I could do this using a temp table but that would be very messy as I > > would really like it to be a single SQL command. > > - -- > > Roy Souther <[EMAIL PROTECTED]> > > http://www.SiliconTao.com > > > > Linux: May the source be with you. > > > > -BEGIN PGP SIGNATURE- > > Version: GnuPG v1.0.6 (GNU/Linux) > > Comment: For info see http://www.gnupg.org > > > > iEYEARECAAYFAj0oo9MACgkQCbnxcmEBt43qFQCgtjCs7khKGH+2LYd78O9mA3h4 > > vDQAn0GkKkuYl1Kybgm/ITO4LbO1WWLX > > =1G4R > > -END PGP SIGNATURE- > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > ---(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])
[SQL] How to get total number of rows when using LIMIT/OFFSET?
Hello, when using LIMIT/OFFSET is it possible to get also the total number of rows besides the actual number of rows of the select? It should be there because the whole list is ordered anyhow. I'm using 7.2.1. Thanks for help, Dirk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Error with DISTINCT and AS keywords
On Wed, 10 Jul 2002, Andreas Schlegel wrote: > Hi, > > I need some help to let this sql statement run with Postgres 7.2.1 > > Doesn't work: > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query Do it as select DISTINCT tnr, titel, 'TEST'::text AS testcol from tTitel; > > If I remove the DISTINCT keyword it works: > select tnr, titel, 'TEST' AS testcol from tTitel; > > > > Greetings, > Andreas > > > > ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Help with function optimisation
"Ian Cass" <[EMAIL PROTECTED]> writes: > I've got a function that I run as a trigger to update a summary table on > insert to the main table. As you can see below, it does a select & an INSERT > if not found, or an UPDATE if found. This currently works OK, but I'd like > to improve performance by removing the SELECT & attempting an UPDATE. If it > fails, I'd like to INSERT instead. Can this be done? Sure. Do the UPDATE, check how many rows updated; if none, INSERT. Finding out how many rows were updated involves a plpgsql command "GET DIAGNOSTICS varname = ROW_COUNT" (gotta love these Oracle-derived syntaxes :-() regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Error with DISTINCT and AS keywords
On Wednesday 10 Jul 2002 1:25 pm, Achilleus Mantzios wrote: > On Wed, 10 Jul 2002, Andreas Schlegel wrote: > > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > > Use an explicit ordering operator or modify the query > > Do it as > > select DISTINCT tnr, titel, 'TEST'::text AS testcol from tTitel; Does anyone know what type a quoted literal has? The error message reports "unknown" - is that because it may be a date? It does strike me as a little unexpected that a quoted string doesn't default to text. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error with DISTINCT and AS keywords
Richard Huxton <[EMAIL PROTECTED]> writes: > Does anyone know what type a quoted literal has? It's assigned the placeholder type UNKNOWN until the parser can figure out from context what type it should be. Unfortunately in this case there's no context the parser knows how to work with. > It does strike me as a little > unexpected that a quoted string doesn't default to text. Yeah, I have a TODO item to let unknown be resolved to text when the only available context is an ORDER BY (or equivalently DISTINCT) request. I've looked at this a little bit but not figured out where the cleanest place to do it is. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Error with DISTINCT and AS keywords
PostgreSQL does not know how to sort 'TEST'. You must help it be telling it what tpe it is. Add ::text after 'TEST' as in 'TEST'::text. Maybe PostgreSQL should default to text for unknown types... JLL Andreas Schlegel wrote: > > Hi, > > I need some help to let this sql statement run with Postgres 7.2.1 > > Doesn't work: > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query > > If I remove the DISTINCT keyword it works: > select tnr, titel, 'TEST' AS testcol from tTitel; > > Greetings, > Andreas > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Waiting for Update
JGM wrote: > > 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? How long since you vacuumed that table? How big are the rows? Are there triggers, constraints, anything fancy? How many indexes does foo have? 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 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
Thanks!
But I did not make long form works also, is it:
pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile
???
msg:
pg_restore:[archiver] could open input file: No such file or directory
could you give out a example of long form
Thanks again.
Jie Liang
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 09, 2002 7:59 PM
To: Jie Liang
Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: 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
[SQL] BETWEEN bug?
Folks, Why does BETWEEN only work for ascending criteria? For example: jwnet=> select '2002-06-07'::TIMESTAMP BETWEEN '2002-06-29'::TIMESTAMP and '2002-06-01'::TIMESTAMP; ?column? -- f (1 row) jwnet=> select '2002-06-07'::TIMESTAMP BETWEEN '2002-06-01'::TIMESTAMP and '2002-06-29'::TIMESTAMP; ?column? -- t (1 row) The above behaviour does not seem logical; is this a SQL spec thing, or a bug? -- -Josh Berkus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Insert Function
Is there anyway to create a insert function? I am trying: CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4) RETURNS int4 AS 'INSERT INTO usr (user_name,first_name,last_name,permission_set_id,customer_id) values ($1,$2,$3,$4,$5)' language 'sql'; and get: ERROR: function declared to return integer, but final statement is not a SELECT I thought that a insert would return a internal row #, but I am not sure about this. Thanks, David Durst MIS Manager www.la-rubber.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] BETWEEN bug?
On Wed, 10 Jul 2002, Josh Berkus wrote: > Folks, > > Why does BETWEEN only work for ascending criteria? For example: > > jwnet=> select '2002-06-07'::TIMESTAMP BETWEEN '2002-06-29'::TIMESTAMP and > '2002-06-01'::TIMESTAMP; > ?column? > -- > f > (1 row) > > jwnet=> select '2002-06-07'::TIMESTAMP BETWEEN '2002-06-01'::TIMESTAMP and > '2002-06-29'::TIMESTAMP; > ?column? > -- > t > (1 row) > > The above behaviour does not seem logical; is this a SQL spec thing, or a bug? Spec thing. In SQL92, "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z". In SQL99, there's BETWEEN SYMMETRIC and ASYMMETRIC, but ASYMMETRIC is the default which is the same as the SQL92 version afaics. Symmetric is an optional feature that I think Christopher's been working on. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Insert Function
On Wednesday 10 July 2002 21:59, David Durst wrote: > Is there anyway to create a insert function? > I am trying: > CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4) > RETURNS int4 AS 'INSERT INTO usr > (user_name,first_name,last_name,permission_set_id,customer_id) values > ($1,$2,$3,$4,$5)' language 'sql'; > > and get: > > ERROR: function declared to return integer, but final statement is not a > SELECT > I thought that a insert would return a internal row #, but I am not sure > about this. You can do something like this: CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4) RETURNS VARCHAR AS 'INSERT INTO usr (user_name,first_name,last_name,permission_set_id,customer_id) VALUES ($1,$2,$3,$4,$5); SELECT ''created user ''|| $1::VARCHAR' language 'sql'; HTH Ian Barwick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] BETWEEN bug?
Stephan, > Spec thing. > > In SQL92, > "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z". > > In SQL99, there's BETWEEN SYMMETRIC and ASYMMETRIC, but ASYMMETRIC is the > default which is the same as the SQL92 version afaics. Symmetric is an > optional feature that I think Christopher's been working on. Thanks. Thought it was something like that. Thank you guys, though, OVERLAPS is asymmetric. -- -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
[SQL] Returning rows from functions
I was wondering if there was a way of returning a complete row from a function, in reading the documentation of CREATE FUNCTION. I was under the impression that you could return a row by using setof, but this does not seem to be true. Can anyone help? ---(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] How to get total number of rows when using LIMIT/OFFSET?
Dirk Lutzebaeck wrote: > > Hello, > > when using LIMIT/OFFSET is it possible to get also the total number of rows > besides the actual number of rows of the select? It should be there > because the whole list is ordered anyhow. I'm using 7.2.1. No way to do that. Perhaps a CURSOR and look at the total count. Not sure if that works but I think it should. -- 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]
Re: [SQL] pg_restore cannot restore index
Jie Liang wrote:
> Thanks!
> But I did not make long form works also, is it:
> pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile
> ???
> msg:
> pg_restore:[archiver] could open input file: No such file or directory
Strange. I found a few more problems with the getopt values in
pg_restore.c not matching the 'case' statement or the documentation.
I got it working here with my patched version using:
pg_restore -I aa -d test /bjm/x
It can't find the file? I didn't fix anything in that area. I am
confused how that could be messed up. I don't see any other meaningful
changes to pg_restore except the quote fixes I did. Are you sure the
file is correct?
Thanks for pointing these things out. It is a big help. Now I am
starting to wonder what else is wrong in the code. :-)
>
>
> could you give out a example of long form
>
>
> Thanks again.
>
>
> Jie Liang
>
> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 09, 2002 7:59 PM
> To: Jie Liang
> Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> Subject: Re: 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
Re: [SQL] BETWEEN bug?
Stephan, > Thanks. Thought it was something like that. > > Thank you guys, though, OVERLAPS is asymmetric. Err ... I meant "symmetric". -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Returning rows from functions
David Durst wrote: > I was wondering if there was a way of returning a complete row from a > function, in reading the documentation of CREATE FUNCTION. I was under the > impression that you could return a row by using setof, but this does not > seem to be true. > Can anyone help? The short answer is "yes, but..."; see the thread at: http://archives.postgresql.org/pgsql-interfaces/2002-06/msg00042.php for a recent discussion about this. HTH, Joe ---(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] Insert Function
On Wednesday 10 July 2002 23:04, David Durst wrote: > I am not sure what the end select does, so if you can give me a explination > it would be apreciated It is there to satisfy the requirement that the function should return a value from a select statement: "12.2. Query Language (SQL) Functions SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list, which must be a SELECT. ..." (see http://www.postgresql.org/idocs/index.php?xfunc-sql.html ) After an INSERT statement you need to provide an arbitrary select statement, which could return anything. All the example does is return a string telling you what you've just done. Ian Barwick > > On Wednesday 10 July 2002 21:59, David Durst wrote: > >> Is there anyway to create a insert function? > >> I am trying: > >> CREATE FUNCTION > >> add_user(varchar(20),varchar(20),varchar(20),int4,int4) RETURNS int4 > >> AS 'INSERT INTO usr > >> (user_name,first_name,last_name,permission_set_id,customer_id) values > >> ($1,$2,$3,$4,$5)' language 'sql'; > >> > >> and get: > >> > >> ERROR: function declared to return integer, but final statement is > >> not a SELECT > >> I thought that a insert would return a internal row #, but I am not > >> sure about this. > > > > You can do something like this: > > > > CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4) > > RETURNS VARCHAR AS > > 'INSERT INTO usr > > (user_name,first_name,last_name,permission_set_id,customer_id) > > VALUES > > ($1,$2,$3,$4,$5); > > SELECT ''created user ''|| $1::VARCHAR' > > language 'sql'; > > > > HTH > > > > Ian Barwick > > > > > > ---(end of > > broadcast)--- TIP 5: Have you checked our > > extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
