[SQL] Failing query...
Hi All, I was hoping someone here would be able to help me with a query I have that is failing with the error: psql:query:1: ERROR: ExecEvalExpr: unknown expression type 501 The query is: SELECT a.auction_id, a.user_id, c.other_names, c.surname, c.email, a.reserve, a.close_time, a.short_desc, a.long_desc, a.start_time, (COALESCE((select MAX(bid) from bid where auction_id = a.auction_id group by auction_id), 0)) as max_bid FROM Auction a, Customer c WHERE a.user_id = c.user_id AND a.auction_id = 754; If I run the following however, SELECT a.auction_id, a.user_id, c.other_names, c.surname, c.email, a.reserve, a.close_time, a.short_desc, a.long_desc, a.start_time FROM Auction a, Customer c WHERE a.user_id = c.user_id AND a.auction_id = 754; I get: auction_id | user_id | other_names | surname |email | reserve | close_time | short_desc | long_de sc | start_time +-+-+---+-+- -++---+- + 754 | 4 | raymond | villarica | [EMAIL PROTECTED] | 22000.00 | 2000-08-17 17:12:16+10 | 8 Avona Av Glebe 2037 | other residential 70 8 square metres | 1990-01-01 00:00:00+11 (1 row) If I run: select MAX(bid) from bid where auction_id = 754; I get: max 310.00 (1 row) Can someone please help me understand what I'm doing wrong. If there is no value in the select max() subselect, the query works, but if there is, it doesn't. Please CC: me to any reply as I've emailed to join the list but the reply hasn't yet reached me from majordomo. Thanks, Shaun
[SQL] CREATE USER in side a TRIGGER FUNCTION
Hi I want to put login and user managment into the database for security reasons. I have a employee table. When I add a person to the employee table I want to create them in the database and when I remove a person I want to drop the person from the database also. How do you do it and what is wrong the the following code? shaun here is the add person trigger. CREATE FUNCTION insuser () RETURNS TRIGGER AS 'BEGIN IF NEW.role = ''clerk'' THEN EXECUTE CREATE USER NEW.login IN GROUP gp_clerk; ELSIF NEW.role = ''medic'' THEN EXECUTE CREATE USER NEW.login IN GROUP gp_medic; ELSIF NEW.role = ''super'' THEN CREATE USER NEW.login IN GROUP gp_super; ELSIF NEW.role = ''admin'' THEN CREATE USER NEW.login CREATEUSER IN GROUP gp_admin; ELSIF NEW.role = ''maint'' THEN CREATE USER NEW.login CREATEUSER IN GROUP gp_maint; END IF; RETURN NEW; END;' LANGUAGE plpgsql; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] selecting problems
What I am trying to do is select all the rows out of the categories table (see below) and select the sum of j_amount out of the judgment table. Right now it is only returning the categories that are in the judgment table. I want it to return those fields with the amounts out of the judgment table, but also the all the other categories from the categories table with 0 as there amount. Does anyone have a suggestion. select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle from categories LEFT OUTER JOIN judgment ON (j_category=ca_code) where j_case_no='45698' and j_party_no=1 group by ca_code,ca_desc,ca_dis_cycle Thanks, Shaun ----- Shaun, <mailto:[EMAIL PROTECTED]> Phone 1-317-913-4160 Fax 1-317-913-4175 CSI - Computer Systems, Inc. <http://www.computer-systems.com/> "Dictionary is the only place that success comes before work. Hard work is the price we must pay for success. I think you can accomplish anything if you're willing to pay the price." Vince Lombardi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Blank-padding
I also have an issue with the blank padding at the end of my fields. Is there any way to eliminate the blank padding at the end of character fields in a table. Such as you have field X as a char(6), but storing "abc" in it. Well postgres seems to add the padding on the end of string that is being stored. So it is stored as "abc " instead of "abc". I don't want that padding there. I am fairly new to Postgres and have only dealt with Informix database systems, which don't store data this way. Any help is very much appreciated. Thanks, Shaun Shaun Watts Programmer/Analyst CSI - Computer Systems, Inc. Phone: 317.913.4160 12975 Parkside Drive Fax: 317.913.4175 Fishers, IN 46038 Toll Free: 800.860.1274 "To give anything less than your best is to sacrifice the gift." -- Steve Prefontaine -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Monday, October 24, 2005 9:46 AM To: Chris Travers Cc: Tom Lane; Dean Gibson (DB Administrator); pgsql-sql Subject: Re: [SQL] Blank-padding On Sat, 2005-10-22 at 00:39, Chris Travers wrote: > Tom Lane wrote: > > >"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > > > > > >>I remember that discussion, and I was for the change. However, upon > >>doing some testing after reading the above, I wonder if the > >>blank-stripping isn't too aggressive. I have a CHAR(6) field (say, > >>named Z) that has "abc " in it. Suppose I want to append "x" to Z, > >>with any leading spaces in Z PRESERVED. > >> > >> > > > >(You meant trailing spaces, I assume.) Why exactly would you want to > >do that? You decided by your choice of datatype that the trailing > >spaces weren't significant. > > > I once built a telecom billing app where this might be important > (fixed length fields). Lets say you have fixed length fields defined > as > char(n) datatypes. You may want to build a query to generate billing > records like: > select field1 || field2 || field3 || field4 || field5 ... AS > bill_record FROM lec_billing_entries; > > It seels to me that I would expect trailing spaces to be preserved in > these cases. Having an implicit rtrim function is asking for problems. > Personally I would rather have to call rtrim explicitly than have the > backend treat the concatenation differently than if I do it on the client. If I rememberate correctificantly, this problem is or was caused by the || operator using the same internal logic for varchar / text AND char. Tom, is it reasonable / doable to have || have a different set of internal functions for each of those types. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Union Question
Hello, I need to union three PostgreSQL tables and this won't be a problem but the tables are on different servers. Basically, I have an administrative server that needs the tables viewable in a web administrator and three query servers that log the needed data locally. Is there a way I can do this without using Slony-I to replicate the data to the administrative server? Shaun McCloud - Software Testing Analyst GeoComm Inc. 601 W. Saint Germain St., Saint Cloud, MN 56301 Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666 click here to visit www.geo-comm.com<http://www.geo-comm.com/> Microsoft Certified Desktop Support Technician (MCDST) Do or do not, there is no try. -Yoda
Re: [SQL] Union Question
I'm trying that, but I am getting an error that says "ERROR: function dblink_connect(unknown, unknown) does not exist" Shaun McCloud - Software Testing Analyst GeoComm Inc. 601 W. Saint Germain St., Saint Cloud, MN 56301 Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666 click here to visit www.geo-comm.com<http://www.geo-comm.com/> Microsoft Certified Desktop Support Technician (MCDST) Do or do not, there is no try. -Yoda From: Plugge, Joe R. [mailto:[email protected]] Sent: Friday, December 03, 2010 10:58 To: Shaun McCloud; [email protected] Subject: RE: Union Question You may want to try dblink. http://www.postgresql.org/docs/current/static/dblink.html From: [email protected] [mailto:[email protected]] On Behalf Of Shaun McCloud Sent: Friday, December 03, 2010 10:51 AM To: [email protected] Subject: [SQL] Union Question Hello, I need to union three PostgreSQL tables and this won't be a problem but the tables are on different servers. Basically, I have an administrative server that needs the tables viewable in a web administrator and three query servers that log the needed data locally. Is there a way I can do this without using Slony-I to replicate the data to the administrative server? Shaun McCloud - Software Testing Analyst GeoComm Inc. 601 W. Saint Germain St., Saint Cloud, MN 56301 Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666 click here to visit www.geo-comm.com<http://www.geo-comm.com/> Microsoft Certified Desktop Support Technician (MCDST) Do or do not, there is no try. -Yoda
Re: [SQL] Union Question
That would be nice to see in the documentation for dblink Shaun McCloud - Software Testing Analyst GeoComm Inc. 601 W. Saint Germain St., Saint Cloud, MN 56301 Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666 click here to visit www.geo-comm.com Microsoft Certified Desktop Support Technician (MCDST) Do or do not, there is no try. -Yoda -Original Message- From: Christophe Pettus [mailto:[email protected]] Sent: Friday, December 03, 2010 11:08 To: Shaun McCloud Cc: Plugge, Joe R.; [email protected] Subject: Re: [SQL] Union Question On Dec 3, 2010, at 9:04 AM, Shaun McCloud wrote: > I'm trying that, but I am getting an error that says "ERROR: function > dblink_connect(unknown, unknown) does not exist" dblink is a contrib module, and needs to be installed before use: http://www.postgresql.org/docs/9.0/interactive/dblink.html -- -- Christophe Pettus [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Union Question
Ah, ok. My bad for not reading good enough. Shaun McCloud - Software Testing Analyst GeoComm Inc. 601 W. Saint Germain St., Saint Cloud, MN 56301 Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666 click here to visit www.geo-comm.com Microsoft Certified Desktop Support Technician (MCDST) Do or do not, there is no try. -Yoda -Original Message- From: Christophe Pettus [mailto:[email protected]] Sent: Friday, December 03, 2010 11:27 To: Shaun McCloud Cc: Plugge, Joe R.; [email protected] Subject: Re: [SQL] Union Question On Dec 3, 2010, at 9:08 AM, Shaun McCloud wrote: > That would be nice to see in the documentation for dblink It's true of all contrib modules; that's mentioned at the start of the contrib section: http://www.postgresql.org/docs/9.0/interactive/contrib.html -- -- Christophe Pettus [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
