Re: [SQL] Wicked screensaver
See the attached file for details ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Approved
Please see the attached file for details. ---(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] "SELECT IN" Still Broken in 7.4b
I'm sure many on this list are sick of hearing about this problem, but it was on the fix list for 7.4, but doesn't appear to have been changed. You can see one of the many threads on the problem at: http://archives.postgresql.org/pgsql-sql/2003-05/msg00352.php Basically, queries of the form SELECT FROM WHERE IN () take forever for high numbers of rows in the IN clause. We've done timing on 7.3 and 7.4b and there is no speed improvement on these queries. Does anyone know what the status of this bug is? -- ___ Front Logic Inc. Tel: 306.653.2725 x14 226 Pacific Ave or 1.800.521.4510 x14 Saskatoon, SK Fax: 306.653.0972 S7K 1N9 Canada Cell: 306.717.2550 http://www.frontlogic.com [EMAIL PROTECTED] ---(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] "SELECT IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Mike Winter wrote: > I'm sure many on this list are sick of hearing about this problem, but it > was on the fix list for 7.4, but doesn't appear to have been changed. IN (subselect) was changed for 7.4 (although I'm not sure of the list mentions the difference). I don't know of any major changes to IN (valuelist) though. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Stephan Szabo wrote: > > On Wed, 20 Aug 2003, Mike Winter wrote: > > > I'm sure many on this list are sick of hearing about this problem, but it > > was on the fix list for 7.4, but doesn't appear to have been changed. > > IN (subselect) was changed for 7.4 (although I'm not sure of the list > mentions the difference). I don't know of any major changes to IN > (valuelist) though. Thanks, Stephan. I was really hoping that the IN(valuelist) was going to be changed at the same time, because it really is unusable for anything over a couple of thousand values. -- ___ Front Logic Inc. Tel: 306.653.2725 x14 226 Pacific Ave or 1.800.521.4510 x14 Saskatoon, SK Fax: 306.653.0972 S7K 1N9 Canada Cell: 306.717.2550 http://www.frontlogic.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] "SELECT IN" Still Broken in 7.4b
> Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > be changed at the same time, because it really is unusable for anything > over a couple of thousand values. Changed to do what? I suppose that the ability to combine several index scans via a bitmap would help to linearize those, but that is far from an IN(valuelist) specific enhancement. signature.asc Description: This is a digitally signed message part
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Ensure your IN list is unique. You might find better times by through an indexed temp table. On Wed, 2003-08-20 at 16:32, Mike Winter wrote: > I'm sure many on this list are sick of hearing about this problem, but it > was on the fix list for 7.4, but doesn't appear to have been changed. > > You can see one of the many threads on the problem at: > http://archives.postgresql.org/pgsql-sql/2003-05/msg00352.php > > Basically, queries of the form SELECT FROM WHERE IN > () take forever for high numbers of rows in the IN clause. > We've done timing on 7.3 and 7.4b and there is no speed improvement on > these queries. > > Does anyone know what the status of this bug is? signature.asc Description: This is a digitally signed message part
[SQL] Before/After Trigger User Switching
When an operation is done throug a SECURITY DEFINER style function and causes a trigger the current_user depends on whether it is executed BEFORE or AFTER. I currently run 7.3.2. I believe the BEFORE behavior is correct while the AFTER behavior is wrong. A bug? already fixed? regards, Aasmund. CREATE TABLE a (b text); CREATE OR REPLACE FUNCTION public.ut () RETURNS TRIGGER SECURITY INVOKER LANGUAGE 'plpgsql' AS' DECLARE t TEXT; t2 TEXT; BEGIN t := current_user; t2 := session_user; RAISE NOTICE ''% Current: % Session: %'', TG_WHEN, t, t2; RETURN NEW; END '; CREATE TRIGGER "ut_trig_before" BEFORE UPDATE OR INSERT OR DELETE ON a FOR EACH ROW EXECUTE PROCEDURE public.ut(); CREATE TRIGGER "ut_trig_after" AFTER UPDATE OR INSERT OR DELETE ON a FOR EACH ROW EXECUTE PROCEDURE public.ut(); CREATE OR REPLACE FUNCTION public.at_test () RETURNS TEXT SECURITY DEFINER LANGUAGE 'sql' AS' INSERT INTO a VALUES (current_user); SELECT session_user::TEXT || ''/'' ||current_user::TEXT; '; db=> SELECT at_test(); NOTICE: BEFORE Current: godal Session: www NOTICE: AFTER Current: www Session: www at_test --- www/godal (1 row) Aasmund Midttun Godal [EMAIL PROTECTED] - http://godal.com +47 40 45 20 46 - Leiv Tronstadsvei 2, NO-7051 Trondheim ---(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] "SELECT IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Rod Taylor wrote: > > Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > > be changed at the same time, because it really is unusable for anything > > over a couple of thousand values. > > Changed to do what? One possibility might be to act as if the valuelist was a table and do the IN as if it were that way, rather than treating it as a set of ORs. That would be basically like doing the temporary table solution, but without requiring the user to do it. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Rod Taylor wrote: > Ensure your IN list is unique. You might find better times by through > an indexed temp table. That is what I ended up doing, but it's not a very elegant solution. MySQL does queries of this type orders of magnitudes faster than Postgres on large value lists, although I have no specific algorithmic solutions to offer for how to make it faster. I don't believe making the IN lists unique has any affect on performance. -- ___ Front Logic Inc. Tel: 306.653.2725 x14 226 Pacific Ave or 1.800.521.4510 x14 Saskatoon, SK Fax: 306.653.0972 S7K 1N9 Canada Cell: 306.717.2550 http://www.frontlogic.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Mike Winter wrote: On Wed, 20 Aug 2003, Rod Taylor wrote: Ensure your IN list is unique. You might find better times by through an indexed temp table. That is what I ended up doing, but it's not a very elegant solution. MySQL does queries of this type orders of magnitudes faster than Postgres on large value lists, although I have no specific algorithmic solutions to offer for how to make it faster. I don't believe making the IN lists unique has any affect on performance. I have no idea whether it will be better or worse performance, but in 7.4 you could do: select blah from foo where id = any (ARRAY[list_of_literals]); Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Before/After Trigger User Switching
I have upgraded to 7.3.4 and the problem persists. Aasmund Midttun Godal writes: When an operation is done throug a SECURITY DEFINER style function and causes a trigger the current_user depends on whether it is executed BEFORE or AFTER. I currently run 7.3.2. I believe the BEFORE behavior is correct while the AFTER behavior is wrong. A bug? already fixed? regards, Aasmund. CREATE TABLE a (b text); CREATE OR REPLACE FUNCTION public.ut () RETURNS TRIGGER SECURITY INVOKER LANGUAGE 'plpgsql' AS' DECLARE t TEXT; t2 TEXT; BEGIN t := current_user; t2 := session_user; RAISE NOTICE ''% Current: % Session: %'', TG_WHEN, t, t2; RETURN NEW; END '; CREATE TRIGGER "ut_trig_before" BEFORE UPDATE OR INSERT OR DELETE ON a FOR EACH ROW EXECUTE PROCEDURE public.ut(); CREATE TRIGGER "ut_trig_after" AFTER UPDATE OR INSERT OR DELETE ON a FOR EACH ROW EXECUTE PROCEDURE public.ut(); CREATE OR REPLACE FUNCTION public.at_test () RETURNS TEXT SECURITY DEFINER LANGUAGE 'sql' AS' INSERT INTO a VALUES (current_user); SELECT session_user::TEXT || ''/'' ||current_user::TEXT; '; db=> SELECT at_test(); NOTICE: BEFORE Current: godal Session: www NOTICE: AFTER Current: www Session: www at_test --- www/godal (1 row) Aasmund Midttun Godal [EMAIL PROTECTED] - http://godal.com +47 40 45 20 46 - Leiv Tronstadsvei 2, NO-7051 Trondheim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) Aasmund Midttun Godal [EMAIL PROTECTED] - http://godal.com +47 40 45 20 46 - Leiv Tronstadsvei 2, NO-7051 Trondheim ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Inheritance or no inheritance, there is a question
Josh Berkus <[EMAIL PROTECTED]> wrote: > Vernon, > >> What is the best solution for this DB scheme problem? > > Have you considered not using inheritance? As a relational-SQL geek myself, > I'm not keen on inheritance -- I feel it mucks up the relational model. Not > everyone agrees with me, of course. > > Personally, I'd suggest the following structure: > > Profile A >id Primary Key >detail1 >detail2 > > Profile B >id Primary Key references Profile A ( ID ) >detail 3 >detail 4 >detail 5 > > Profile Languages >id not null references profile A ( ID ) >language id >primary key id, language id > > etc. > > In this way, Profile B is a child table with a 1:0-1 relationship > with Profile A. Multi-value dependancies, like Languages, can be > related to either the people who belong to the B group (and, by > implication, the B group) or the people who belong to the A group > only. > > Want the B group? SELECT A JOIN B > Want the A group only? SELECT A EXCEPT B > > This is the "relational" way to approach the problem. Grewvy! I've been running a system that takes various kinds of payments, some tables of which are below. INSERTs & UPDATEs only happen on the tables that inherit from the payment table. To sum up or otherwise do reports, I SELECT from the payment table. Is there some relational way to do this without ripping my hair out every time I want to do a new query? As some of you know, I don't have much hair left to lose ;) CREATE TABLE payment ( payment_id SERIAL NOT NULL PRIMARY KEY , order_id INTEGER NOT NULL REFERENCES order(order_id) ON DELETE RESTRICT , amount INTEGER NOT NULL -- pennies , payment_date DATE NOT NULL DEFAULT now() ); CREATE TABLE payment_check ( check_no INTEGER NOT NULL , payer_name VARCHAR(255) NOT NULL ) INHERITS (payment); CREATE TABLE payment_money_order ( issuer VARCHAR(255) NOT NULL , mo_num VARCHAR(64) NOT NULL ) INHERITS (payment); CREATE TABLE payment_wire ( payment_wire_desc VARCHAR(255) NOT NULL ) INHERITS (payment); Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 Fascism should more properly be called corporatism, since it is the merger of state and corporate power. Benito Mussolini ---(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] Table conversion query...
Hello everyone, I have a table that looks like: date tran glamt 08/20/03 1001 3010 -30.00 08/20/03 1001 1030 -300.00 08/20/03 1001 1060 +330.00 08/20/03 1002 ...next transaction ... and I need to convert to: date glcr gldb amt 08/20/03 1060 3010 30.00 08/20/03 1060 1030 300.00 in other words, the negative gl's go into gldb and they make up the total for the positive gl. is there a way to accomplish this in postgresql? or should I implement it inside the java app? thanks george __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Porting from PL/SQL to PLPGSQL
Hi All, We are in the process of migrating of our application from Oracle to PostGreSQL. we are facing a few problems with PL/SQL Code.. 1) In Oracle sqlplus we can run sql script files as @script_name; How do we do the same in PostGres. Also is there any replacement for "&&" in PostGres ? Eg: CREATE USER CATALOG IDENTIFIED BY &ORA_PASSWORD DEFAULT TABLESPACE &DFLT_TABLESPACE TEMPORARY TABLESPACE &TEMP_TABLESPACE 2) In PostGres a function can not take more 16 arguments.We have some procedures which are taking more than 16 arguements.So how can we convert them into PostGres. Can anyone give some valuable suggestions.. Regards Joe. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Before/After Trigger User Switching
"Aasmund Midttun Godal" <[EMAIL PROTECTED]> writes: > When an operation is done throug a SECURITY DEFINER style function and > causes a trigger the current_user depends on whether it is executed BEFORE > or AFTER. I currently run 7.3.2. I believe the BEFORE behavior is correct > while the AFTER behavior is wrong. A bug? already fixed? An AFTER trigger is not fired until the interactive statement is about to complete --- ie, after the SECURITY DEFINER function has returned. There has been previous discussion (inconclusive) about changing the time of invocation of AFTER triggers, but given the current timing this is the behavior I'd expect. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Reverse pattern match.
Many thanks... got it now.
Example...
sox=# select * from regexpatt;
pattern |description
-+
^b | starts with a B
^a | starts with an A
^c.*l$ | starts with a C and ends with an L
(3 rows)
sox=# select description from regexpatt where 'bravo' ~ pattern;
description
starts with a B
(1 row)
sox=# select description from regexpatt where 'caramel' ~ pattern;
description
starts with a C and ends with an L
(1 row)
"Josh Berkus" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Moonstruck,
> I want to create a table of regular expression patterns (for assessing
> phone numbers), something like:
> CREATE TABLE CallType ( pattern varchar primary key,
> typevarchar,
> rateint4);
> INSERT INTO CallType VALUES ('0[3-9]','Interstate Call',50);
> INSERT INTO CallType VALUES ('9___','Local Call',25);
> INSERT INTO CallType VALUES ('0011__%','International Call',100);
PostgreSQL supports real Regular Expressions, via the ~ operator. See "~"
under "functions and operators", and then consult your favorite book or
online ressouce on how to compose regexps.
An example of "is not in 415 area code" would be
phone_no ~ '^415\d{7}'
which should be "415" at the beginning followed by at least 7 other digits.
(Folks, please correct my regex code if it's bad!)
The disadvantage to this approach is that it cannot be indexed.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Table conversion query...
George McQuade wrote: date tran glamt 08/20/03 1001 3010 -30.00 08/20/03 1001 1030 -300.00 08/20/03 1001 1060 +330.00 08/20/03 1002 ...next transaction ... and I need to convert to: date glcr gldb amt 08/20/03 1060 3010 30.00 08/20/03 1060 1030 300.00 in other words, the negative gl's go into gldb and they make up the total for the positive gl. is there a way to accomplish this in postgresql? or should I implement it inside the java app? There's no simple way to do this in Postgres. You could do it with a PL/pgSQL table function, or for better performance a C function. There are a couple of questions yet to be answered though: 1) Can there ever be more than one credit account, e.g. -30, -300, +150, +180? 2) What happens if sum(neg values) != sum(pos values)? Throw an error? Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Mike Winter <[EMAIL PROTECTED]> writes: > MySQL does queries of this type orders of magnitudes faster than Postgres > on large value lists, although I have no specific algorithmic solutions to > offer for how to make it faster. How large is "large", and what plan type are you getting (seq scan or multiple index scan)? Is it possible that the cost comes from planner overhead and not execution? Checking EXPLAIN ANALYZE reported time against actual elapsed time (cf psql's \timing option) would tell. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Before/After Trigger User Switching
Thank you for your quick reply! I understand your point of view, however the fact remains that you want the action to be done as though it was the DEFINER user that did it, and that has not changed even thoug the function itself has finished? regards, aasmund. Tom Lane writes: "Aasmund Midttun Godal" <[EMAIL PROTECTED]> writes: When an operation is done throug a SECURITY DEFINER style function and causes a trigger the current_user depends on whether it is executed BEFORE or AFTER. I currently run 7.3.2. I believe the BEFORE behavior is correct while the AFTER behavior is wrong. A bug? already fixed? An AFTER trigger is not fired until the interactive statement is about to complete --- ie, after the SECURITY DEFINER function has returned. There has been previous discussion (inconclusive) about changing the time of invocation of AFTER triggers, but given the current timing this is the behavior I'd expect. regards, tom lane Aasmund Midttun Godal [EMAIL PROTECTED] - http://godal.com +47 40 45 20 46 - Leiv Tronstadsvei 2, NO-7051 Trondheim ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, 2003-08-20 at 17:41, Stephan Szabo wrote: > On Wed, 20 Aug 2003, Rod Taylor wrote: > > > > Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > > > be changed at the same time, because it really is unusable for anything > > > over a couple of thousand values. > > > > Changed to do what? > > One possibility might be to act as if the valuelist was a table and do the > IN as if it were that way, rather than treating it as a set of ORs. That > would be basically like doing the temporary table solution, but without > requiring the user to do it. Is the temp table version any faster? I realize it has a higher limit to the number of items you can have in the list. signature.asc Description: This is a digitally signed message part
Re: [SQL] Porting from PL/SQL to PLPGSQL
On Wed, Aug 20, 2003 at 03:15:23PM +0530, Jomon Skariah wrote: > > we are facing a few problems with PL/SQL Code.. > > > 1)In Oracle sqlplus we can run sql script files as @script_name; > How do we do the same in PostGres. psql takes \i. See psql's \? See the "Porting From Oracle PL/SQL" document I wrote that is part of the PL/pgSQL documentation. > Also is there any replacement for "&&" in PostGres ? > > > Eg: > CREATE USER CATALOG > IDENTIFIED BY &ORA_PASSWORD > DEFAULT TABLESPACE &DFLT_TABLESPACE > TEMPORARY TABLESPACE &TEMP_TABLESPACE What does that do? > > 2)In PostGres a function can not take more 16 arguments.We have some > procedures which are taking more than > 16 arguements.So how can we convert them into PostGres. You can recompile to change the number of parameters. PG 7.3 can have up to 32 arguments by default, IIRC. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + It's now the GNU Emacs of all terminal emulators. -- Linus Torvalds, regarding the fact that Linux started off as a terminal emulator ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Configuring Problem on Solaris............
Hi, We are trying to install on solaris ver 9.0 we are getting the following error so kindly tell us how to over come this problem. configure: error:*** Could not execute a simple test program. This may be a problem*** related to locating shared libraries. Check the file 'config.log'*** for the exact reason. expecting ur reply at the earliest. Thanks & Regards, Chida
[SQL] link toward pgsql-sql is missing on the left menu athttp://archives.postgresql.org/
Hello, I have noticed that the link toward pgsql-sql is missing on the left menu at http://archives.postgresql.org/. Is that normal? -- *** Richard NAGY Nameshield 46, rue Jean BODIN F-49000 Angers Tél : +33 2 41 18 28 28 *** ---(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] Before/After Trigger User Switching
"Aasmund Midttun Godal" <[EMAIL PROTECTED]> writes: > Thank you for your quick reply! > I understand your point of view, however the fact remains that you want the > action to be done as though it was the DEFINER user that did it, and that > has not changed even thoug the function itself has finished? ... if there's any bug here, I'd argue that it's that we don't force trigger functions to run as the owner of the table they're on. The privileges of the user that did the INSERT or whatever are the wrong thing in any case, I'd say. Which suggests a workaround for the moment: your trigger function should be a SECURITY DEFINER. regards, tom lane ---(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] Configuring Problem on Solaris............
Chidananda writes: > We are trying to install on solaris ver 9.0 we are getting the following > error so kindly tell us how to over come this problem. > > configure: error: > *** Could not execute a simple test program. This may be a problem > *** related to locating shared libraries. Check the file 'config.log' > *** for the exact reason. Please check the file 'config.log' for the exact reason. It may be a problem related to locating certain shared libraries. The archives contain several instances where this problem is dicussed. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] problem with automatic altering of groups
"Matthias Nagl" <[EMAIL PROTECTED]> schrieb am 20.08.03 23:09:03: Hello, is ist possible to write a function that alters the group of a given user like the following should do in my opinion (I'd like to use it in a rule): CREATE FUNCTION set_group (text, integer) RETURNS text AS ' ALTER GROUP userlevel1 DROP USER $1; ALTER GROUP userlevel2 DROP USER $1; ALTER GROUP userlevel3 DROP USER $1; ALTER GROUP userlevel4 DROP USER $1; ALTER GROUP userlevel5 DROP USER $1; CASE $2 WHEN 5 THEN (ALTER GROUP userlevel5 ADD USER $1) WHEN 4 THEN (ALTER GROUP userlevel5 ADD USER $1; ALTER GROUP userlevel4 ADD USER $1;) [...] END; SELECT ''; ' LANGUAGE 'SQL'; I don't understand why this function ist refused by postgresql with an parse error at the first $1 as ist every other method I tryed to get postgres making an "alter group" with a variable. I'd be very happy if someone could help... yours Matthias Nagl __ Die sicherste Form der Kommunikation: E-Mails verschluesseln, Spam-Filter, Adressverifizierung, digitale Unterschrift: http://freemail.web.de ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] problem with automatic altering of groups
Hello, is ist possible to write a function that alters the group of a given user like the following should do in my opinion (I'd like to use it in a rule): CREATE FUNCTION set_group (text, integer) RETURNS text AS ' ALTER GROUP userlevel1 DROP USER $1; ALTER GROUP userlevel2 DROP USER $1; ALTER GROUP userlevel3 DROP USER $1; ALTER GROUP userlevel4 DROP USER $1; ALTER GROUP userlevel5 DROP USER $1; CASE $2 WHEN 5 THEN (ALTER GROUP userlevel5 ADD USER $1) WHEN 4 THEN (ALTER GROUP userlevel5 ADD USER $1; ALTER GROUP userlevel4 ADD USER $1;) [...] END; SELECT ''; ' LANGUAGE 'SQL'; I don't understand why this function ist refused by postgresql with an parse error at the first $1 as ist every other method I tryed to get postgres making an "alter group" with a variable. I'd be very happy if someone could help... yours Matthias Nagl __ Die sicherste Form der Kommunikation: E-Mails verschluesseln, Spam-Filter, Adressverifizierung, digitale Unterschrift: http://freemail.web.de ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Configuring Problem on Solaris............
Send the config.log file. On Wed, 20 Aug 2003 23:18:15 +0200 (CEST) Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Chidananda writes: > > > We are trying to install on solaris ver 9.0 we are getting the following > > error so kindly tell us how to over come this problem. > > > > configure: error: > > *** Could not execute a simple test program. This may be a problem > > *** related to locating shared libraries. Check the file 'config.log' > > *** for the exact reason. > > Please check the file 'config.log' for the exact reason. It may be a > problem related to locating certain shared libraries. The archives > contain several instances where this problem is dicussed. > > -- > Peter Eisentraut [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- Lucas Lain Gerencia de Ingeniería TechTel Telecomunicaciones [EMAIL PROTECTED] TE. (54-11) 4000-3164 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem with automatic altering of groups
Matthias, > I don't understand why this function ist refused by postgresql with an parse error at the first $1 as ist every > other method I tryed to get postgres making an "alter group" with a variable. I'd be very happy if someone > could help... You can't substitute variables for object names. If you need to construct dynamic query strings, use PL/pgSQL and EXECUTE: sql_qry := ''ALTER GROUP '' || $group || '' ADD USER '' || $user; EXECUTE sql_query; -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Rod Taylor wrote: > On Wed, 2003-08-20 at 17:41, Stephan Szabo wrote: > > On Wed, 20 Aug 2003, Rod Taylor wrote: > > > > > > Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > > > > be changed at the same time, because it really is unusable for anything > > > > over a couple of thousand values. > > > > > > Changed to do what? > > > > One possibility might be to act as if the valuelist was a table and do the > > IN as if it were that way, rather than treating it as a set of ORs. That > > would be basically like doing the temporary table solution, but without > > requiring the user to do it. > > Is the temp table version any faster? I realize it has a higher limit > to the number of items you can have in the list. Within the scope of the new hashed IN stuff I believe so in at least some cases. I have a few million row table of integers where searching for values IN (~1 values) takes longer than creating the temp table, copying into it and doing the in subquery. That's not a particularly meaningful test case, but sending the psql output to /dev/null gives me: create temp table/copy 10001 entries/select in subquery - .8 sec select in (value list 9998 entries) - ~ 2min 19 sec explain select in (value list) - ~ 4.8 sec ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table conversion query...
--- Joe Conway <[EMAIL PROTECTED]> wrote: > George McQuade wrote: > > date tran glamt > > 08/20/03 1001 3010 -30.00 > > 08/20/03 1001 1030 -300.00 > > 08/20/03 1001 1060 +330.00 > > 08/20/03 1002 ...next transaction > > ... > > and I need to convert to: > > > > date glcr gldb amt > > 08/20/03 1060 3010 30.00 > > 08/20/03 1060 1030 300.00 > > > > in other words, the negative gl's go into gldb > > and they make up the total for the positive gl. > > > > is there a way to accomplish this in postgresql? > > or should I implement it inside the java app? > > There's no simple way to do this in Postgres. You > could do it with a > PL/pgSQL table function, or for better performance a > C function. Interesting, my C is gone a long time ago. Would the table function be fairly complex for someone who's never done one? I'm tempted by the java option, but initial jdbc speed tests don't look very promising (the avg file has 1/2 million records). > There are a couple of questions yet to be answered > though: > 1) Can there ever be more than one credit account, > e.g. -30, -300, +150, +180? No, so far all the examples I've seen involve a single credit account. > 2) What happens if sum(neg values) != sum(pos > values)? Throw an error? Yes, this would indicate a system out of balance that requires external assistance. Thanks for the help. george __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(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] [Newbie] migrating a stored procedure from MSSQL to postgresql
As declared, your function returns TEXT, i.e. unlimited characters. >> CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS Since your variable >> r_SKUPrice RECORD; contains a number of columns >> SELECT SKU, Price INTO r_SKUPrice you could create a composite TYPE that matches those columns and since your variable can contain a number of such rows, (see the select above) the function needs to become a set returning function CREATE FUNCTION UpdateOrder(INTEGER) RETURNS SETOF AS Rick Bengali wrote: Hi, I am a postgresql and stored procedures beginner and I would like to know if the stored procedure I am trying to migrate to plpgsql from MSSQL is correct. Here 's the only table involved in the stored procedure: create table ManufacturerOrders ( OrderNumber serial, SKU int not null, Make varchar(50) not null, Model varchar(50) not null, Price int not null, Status varchar(20) not null, primary key (OrderNumber) ); Here 's the original MSSQL stored procedure: create procedure UpdateOrder (@OrderNum int) as set nocount on update ManufacturerOrders set Status = "Shipped" where OrderNumber = @OrderNum; SELECT SKU, Price FROM ManufacturerOrders WHERE OrderNumber = @OrderNum go Here 's the plpgsql version i wrote: CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS ' DECLARE i_ordernum ALIAS for $1; r_SKUPrice RECORD; BEGIN update ManufacturerOrders set Status = ''Shipped'' where OrderNumber = i_ordernum; SELECT SKU, Price INTO r_SKUPrice FROM ManufacturerOrders WHERE OrderNumber = i_ordernum; return r_SKUPrice; END; ' LANGUAGE 'plpgsql'; I would like to know especially if the RETURNS statement is correct here and if i can give a name to the record r_SKUPrice columns . Thanks in advance, Bengali ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Table conversion query...
George McQuade wrote:
Interesting, my C is gone a long time ago. Would the
table function be fairly complex for someone who's
never done one?
I'm tempted by the java option, but initial jdbc speed
tests don't look very promising (the avg file has
1/2 million records).
Well this is a fairly complex problem regardless of the language. You
need to be able to accumulate output rows for an arbitrary number of
different debit gls, flush them out whenever the transaction id changes,
and be sure that they reconcile with the credit.
I'm actually not sure you can do this in plpgsql in Postgres 7.3.x -- in
7.4 you could use arrays to accumulate the debit data. Here is a lightly
tested (no warranty, might not work correctly, use at your own risk etc,
etc ;-) ) plpgsql function which seems to work as you want it. Consider
it a starting point, but only if you can use 7.4beta:
create table gl (transdate date, tran int, gl int, amt numeric(9,2));
insert into gl values('08/20/03',1001,3010,-30.00);
insert into gl values('08/20/03',1001,1030,-300.00);
insert into gl values('08/20/03',1001,1060,330.00);
insert into gl values('08/21/03',1002,3010,-30.00);
insert into gl values('08/21/03',1002,1030,-200.00);
insert into gl values('08/21/03',1002,3010,-100.00);
insert into gl values('08/21/03',1002,1060,330.00);
create type reconcile_type as (transdate date, glcr int, gldb int, amt
numeric(9,2));
create or replace function reconcile(text) returns setof reconcile_type as '
declare
v_crit alias for $1;
v_sql text;
v_last_transdate date;
v_last_tran int := 0;
v_last_glcr int := 0;
v_last_glcr_amt numeric(9,2) := 0;
v_last_gldb int[] := ''{}'';
v_last_gldb_amt numeric(9,2)[] := ''{}'';
v_sum_debit numeric(9,2) := 0;
v_glcr_found bool := false;
rec record;
result reconcile_type%rowtype;
i int;
ub int;
begin
if v_crit is not null then
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl where ''
|| v_crit ||
'' group by transdate, tran, gl order by 2,4 desc,1,3'';
else
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl '' ||
''group by transdate, tran, gl order by 2,4 desc,1,3'';
end if;
for rec in execute v_sql loop
if rec.tran != v_last_tran then
-- starting a new tran
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
-- first pass for this tran -- it better be a credit
if rec.amt > 0 then
v_glcr_found := true;
else
RAISE EXCEPTION ''no credit found for transaction: %'', rec.tran;
end if;
v_last_tran := rec.tran;
v_last_transdate := rec.transdate;
v_last_glcr := rec.gl;
v_last_glcr_amt := rec.amt;
v_last_gldb := ''{}'';
v_last_gldb_amt := ''{}'';
v_sum_debit := 0;
else
-- not a new tran
if rec.amt > 0 then
-- if we have already visited the credit, and we see another,
cry foul
RAISE EXCEPTION ''Two credits found for transaction: %'', rec.tran;
else
-- otherwise accumulate the debit
v_last_gldb := v_last_gldb || rec.gl;
v_last_gldb_amt := v_last_gldb_amt || rec.amt;
v_sum_debit := v_sum_debit + rec.amt;
end if;
end if;
end loop;
-- need this to get the last (or only) trans
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
return;
end;
' language plpgsql;
regression=# select * from reconcile(null);
transdate | glcr | gldb | amt
+--+--+-
2003-08-20 | 1060 | 3010 | -30.00
2003-08-20 | 1060 | 1030 | -300.00
2003-08-21 | 1060 | 3010 | -130.00
2003-08-21 | 1060 | 1030 | -200.00
(4 rows)
You could do similar a C function in 7.3.x. I'm not sure how you'd write
this in 7.3.x plpgsql though :(
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] URGENT : free result (libpq++ API)
Hi all, I’m using PG – 7.2.4 on Solaries. I’m using “libpq++” library as client implementation is coded in “C++”. I’m doing the following : Making the connection to database by creating new object of “PgDatabase” i.e. data = new PgDatabase( “ CONNECTION INFO “); Then I execure the query with: int return = data->Exec( “ THIS IS A SQL QUERY”); My question is , How should I clear(free) the result of the query? Need I close the connection after each query to do that? I wish to use the same connection for multiple queries. This problem I’ve observed with “libpq++” only not with “libpq” With “libpq” APIs the, “PQexec” API returns “PGresult” type pointer with which we can clear with the result of the query with “Pqclear(Pgresult *)” API. Is the same functionality is present in “lipq++” APIs also? Pls. help. Thanks, Anagha
Re: [SQL] [ADMIN] URGENT : free result (libpq++ API)
On Thu, 21 Aug 2003, Anagha Joshi wrote:
> Hi all,
> I'm using PG - 7.2.4 on Solaries.
> I'm using "libpq++" library as client implementation is coded in "C++".
>
> I'm doing the following :
> 1.Making the connection to database by creating new object of
> "PgDatabase" i.e.
> data = new PgDatabase( " CONNECTION INFO ");
>
> 2.Then I execure the query with:
> int return = data->Exec( " THIS IS A SQL QUERY");
>
> My question is ,
> How should I clear(free) the result of the query? Need I close the
> connection after each query to do that?
It looks to me that PgConnection::Exec clears an existing query result
when it's called.
// PgConnection::exec -- send a query to the backend
ExecStatusType PgConnection::Exec(const char* query)
{
// Clear the result stucture if needed
if (pgResult)
PQclear(pgResult);
// Execute the given query
pgResult = PQexec(pgConn, query);
// Return the status
if (pgResult)
return PQresultStatus(pgResult);
else
return PGRES_FATAL_ERROR;
}
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
