Re: [SQL] ::text problem
Thanks a lot. It worked. - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Tuesday, November 25, 2003 9:44 PM Subject: Re: [SQL] ::text problem > On Tuesday 25 November 2003 12:37, Kumar wrote: > > > > SELECT > > CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as > > approval_date FROM t1; > > ERROR: Bad timestamp external representation 'Record_Not_Found' > > > > > > SELECT > > CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as > > approval_date FROM t1; > > ERROR: CASE types 'timestamp without time zone' and 'text' not matched > > Almost - you're on the right lines. Try: > SELECT > CASE > WHEN c1 IS NULL THEN 'Record_Not_Found'::text > ELSE c1::text > END as approval_date FROM t1; > > You need to make sure both options return type text. > > -- > Richard Huxton > Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Unsigned numbers
Hello Friends, I have the following questions. I have a lot of fields in my database that are int or float. The problem is that I need them to be only positive (unsigned) and the users try to insert sometimes negative. I know that I can make this with CHECK CONSTRAINT but for some reasons this is not a good solution for me. The reason is that when I have a check constraint on a certain field and someone try to insert negative number the error message contains the name of the constraint and the name of the table but not the name of the field. So on higher level I can't send a proper message to the user. Are there any unsigned data types like unsigned int or unsigned float. Can I make a domain or something like that (How exactly to add constraint to domain). I am using v7.3.2. Thanks! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Unsigned numbers
On Wednesday 26 November 2003 11:10, Kaloyan Iliev Iliev wrote: > Hello Friends, > > I have the following questions. > I have a lot of fields in my database that are int or float. > The problem is that I need them to be only positive (unsigned) and the > users try to insert sometimes negative. [snip - problem is that errors give constraint name] > Are there any unsigned data types like unsigned int or unsigned float. > Can I make a domain or something like that (How exactly to add > constraint to domain). > I am using v7.3.2. Adding CHECKs to domains is new for 7.4, I believe. There aren't any built-in unsigned types, although in theory you could write your own. What I do is format my constraint names in a known way. So, a check on table foo, column bar might be called "foo_bar_positive" - then I can get the information I want from the constraint name. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] trigger conversion advice needed
I'm working on converting a simple trigger from Oracle to Postgres and I have a couple ofl questions that I need some help on please. First here's the Oracle trigger: CREATE OR REPLACE TRIGGER t_ship_date AFTER UPDATE OR INSERT OF order_date ON orders BEGIN UPDATE orders SET ship_date = working_5days(order_date); END; / When I was working on converting the trigger I noticed that Postgres doesn't have the OF table_attribute ON syntax support. I just want the trigger to fire when the order_date field in the order table is updated or inserted like it is specified in the Oracle trigger specification above. So I did this in Postgres: CREATE OR REPLACE FUNCTION t_ship_date() RETURNS TRIGGER AS ' BEGIN UPDATE orders SET ship_date = working_5days(new.order_date); RETURN NEW; END; ' LANGUAGE 'plpgsql'; I justed used new.order_date in the Postgres one version. I'm pretty sure that this isn't going to work, it will probably update every ship_date which is not what I want. I just want to update the ship_date when the record's order_date is updated. Is there some way in Postgres to specify a trigger to fire only when a certain field in a table is changed or inserted (like I had in the Oracle version)? I couldn't find anything for triggers to do that in the docs so I hope someone can shed some light on this for me. I thought maybe something like this could work too (probably closer to being correct): CREATE OR REPLACE FUNCTION t_ship_date() RETURNS TRIGGER AS ' BEGIN IF TG_OP = ''INSERT'' THEN UPDATE orders SET ship_date = working_5days(new.order_date) where order_no = new.order_no; ELSIF TG_OP =''UPDATE'' THEN UPDATE orders SET ship_date = working_5days(new.order_date) where order_no = old.order_no; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; The working_5days function just adds 5 business days to a date fyi. Now the second question I have is due to an error I keep getting when I try and make the trigger definition below: CREATE TRIGGER t_ship_date AFTER UPDATE OR INSERT ON orders EXECUTE PROCEDURE t_ship_date(); I always get a parse error at or near execute. What am I doing wrong? If I could get the trigger to compile then I could test it to see if it works the same as the Oracle version. I wouldn't even have to ask the first question if I could get it to compile but I thought I might as well ask anyway so I don't have to post another message. Thanks for the help. -Clint P.S. 7.3.4 is the postgres version on the server. _ Has one of the new viruses infected your computer? Find out with a FREE online computer virus scan from McAfee. Take the FreeScan now! http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Problem: Postgresql not starting
Hi All I am using postgresql 7.2 on Linux. It does not start when i am trying this /etc/rc.d/init.d/postgresql restart. It give an error saying "postmaster already running". I have done rm -f /var/lib/pgsql/data/postmaster.pid and rm -f /var/run/postmaster.pid But it still says the same. whenever i restart with the above command When say "stop" it says failed and when i say "start" it says postmaster already running. Please help me out --m ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] trigger conversion advice needed
"Clint Stotesbery" <[EMAIL PROTECTED]> writes: > I'm working on converting a simple trigger from Oracle to Postgres and I > have a couple ofl questions that I need some help on please. First here's > the Oracle trigger: > CREATE OR REPLACE TRIGGER t_ship_date > AFTER UPDATE OR INSERT OF order_date ON orders > BEGIN >UPDATE orders >SET ship_date = working_5days(order_date); > END; It looks to me like this trigger implicitly assumes that an UPDATE command would only affect the row it was fired for --- which is not at all how Postgres will interpret such a command. (Alternatively, maybe the trigger actually does result in recomputing every row's ship_date? You would only notice if ship_date had been changed manually in some rows to be different from order_date + 5...) Guessing at what is actually wanted here, my inclination would be to use a BEFORE INSERT OR UPDATE trigger and to detect updates by change from OLD to NEW. The INSERT case would simply do NEW.ship_date := working_5days(NEW.order_date); RETURN NEW; The UPDATE case would look like IF NEW.order_date <> OLD.order_date THEN NEW.ship_date := working_5days(NEW.order_date); END IF; RETURN NEW; Pretty simple when you get the hang of it. > CREATE TRIGGER t_ship_date AFTER UPDATE OR INSERT ON orders > EXECUTE PROCEDURE t_ship_date(); > I always get a parse error at or near execute. You need to say FOR EACH ROW in there too. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Scaler forms as function arguments
Hi, I want to write a function of the following type CREATE FUNCTION test ( ) RETURNS setof MyTable AS 'SELECT * FROM MyTable WHERE id IN $1' LANGUAGE 'SQL' ; I was not able to find a reference whether this is possible and if yes how to specify the argument type and how to call this function to hand over the list for the IN clause correctly. Next question. Is there any example how to return a SETOF MyTable in a plpgsql function? I tried CREATE FUNCTION test2() RETURNS setof MyTable AS ' DECLARE result SETOF MyTable ; BEGIN result := (SELECT * FROM MyTable); RETURN result ; END; ' LANGUAGE 'plpgsql'; wich failed. Kind regards Andreas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Problem: Postgresql not starting
Startup scripts for linux hanging around are sometimes broken. You better write your own. O kyrios [EMAIL PROTECTED] egrapse stis Nov 26, 2003 : > Hi All > I am using postgresql 7.2 on Linux. It does not start when i am trying this > /etc/rc.d/init.d/postgresql restart. > It give an error saying "postmaster already running". > I have done > rm -f /var/lib/pgsql/data/postmaster.pid > and > rm -f /var/run/postmaster.pid > > But it still says the same. whenever i restart with the above command > When say "stop" it says failed and when i say "start" it says postmaster > already running. > Please help me out > > --m > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] trigger conversion advice needed
Thanks for the advice Tom. I figured out why my create trigger statement wouldn't compile. Postgres expects a FOR EACH ROW or FOR EACH STATEMENT clause before the EXECUTE portion. Oracle assumes a statement level trigger unless you specify it to be for each row. In addition Oracle allows for specific columns to be monitored for updates like I had in the Oracle trigger (AFTER UPDATE OR INSERT OF order_date ON orders). Well I ended up adding FOR EACH STATEMENT to the Postgres version after the first email. I got a message back that said statement triggers weren't implemented in Postgres yet even though according to the 7.3 docs it can be used. I looked at the changelog for 7.4 and it said: "Add statement-level triggers (Neil) While this allows a trigger to fire at the end of a statement, it does not allow the trigger to access all rows modified by the statement. This capability is planned for a future release." I wonder if that means that I can specify FOR EACH STATEMENT and have it compile fine but it seems like that I can't acccess new and old though still in 7.4. I'll have to experiment with what you suggested and perhaps look into upgrading to 7.4 as well. Thanks, Clint Original Message Follows From: Tom Lane <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [SQL] trigger conversion advice needed Date: Wed, 26 Nov 2003 10:40:08 -0500 Received: from alias2.acm.org ([199.222.69.92]) by mc11-f24.hotmail.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 26 Nov 2003 07:40:10 -0800 Received: from sss.pgh.pa.us ([192.204.191.242])by alias2.acm.org (ACM Email Forwarding Service) with ESMTP id CRY73883for <[EMAIL PROTECTED]>; Wed, 26 Nov 2003 10:40:10 -0500 Received: from sss2.sss.pgh.pa.us ([EMAIL PROTECTED] [127.0.0.1])by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAQFe819015058;Wed, 26 Nov 2003 10:40:08 -0500 (EST) X-Message-Info: JGTYoYF78jE74k1WFZAS8n73gEHv7D0r In-reply-to: <[EMAIL PROTECTED]> References: <[EMAIL PROTECTED]> Comments: In-reply-to "Clint Stotesbery" <[EMAIL PROTECTED]>message dated "Wed, 26 Nov 2003 11:51:42 +" Message-ID: <[EMAIL PROTECTED]> Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 26 Nov 2003 15:40:10.0631 (UTC) FILETIME=[936E3170:01C3B433] "Clint Stotesbery" <[EMAIL PROTECTED]> writes: > I'm working on converting a simple trigger from Oracle to Postgres and I > have a couple ofl questions that I need some help on please. First here's > the Oracle trigger: > CREATE OR REPLACE TRIGGER t_ship_date > AFTER UPDATE OR INSERT OF order_date ON orders > BEGIN >UPDATE orders >SET ship_date = working_5days(order_date); > END; It looks to me like this trigger implicitly assumes that an UPDATE command would only affect the row it was fired for --- which is not at all how Postgres will interpret such a command. (Alternatively, maybe the trigger actually does result in recomputing every row's ship_date? You would only notice if ship_date had been changed manually in some rows to be different from order_date + 5...) Guessing at what is actually wanted here, my inclination would be to use a BEFORE INSERT OR UPDATE trigger and to detect updates by change from OLD to NEW. The INSERT case would simply do NEW.ship_date := working_5days(NEW.order_date); RETURN NEW; The UPDATE case would look like IF NEW.order_date <> OLD.order_date THEN NEW.ship_date := working_5days(NEW.order_date); END IF; RETURN NEW; Pretty simple when you get the hang of it. > CREATE TRIGGER t_ship_date AFTER UPDATE OR INSERT ON orders > EXECUTE PROCEDURE t_ship_date(); > I always get a parse error at or near execute. You need to say FOR EACH ROW in there too. regards, tom lane _ Set yourself up for fun at home! Get tips on home entertainment equipment, video game reviews, and more here. http://special.msn.com/home/homeent.armx ---(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
[SQL] Postgres: MS Access and Tomcat
Hi All I am using postgres database. Users of the DB include Web users thro tomcat and internal user thr'o MS Access. The problem is the users of Access sometimes do not close these connections and postgres fails saying too many connections for the web users. Is there a way i can resolve this by uncommenting the #Max_connections=32 in the postgresql.conf. What are the effects of uncommenting this part in postgresql.conf. Do i need to make any more changes when uncomment the Max_connections=32 in the postgresql.conf file Thank you very much --Mohan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Scaler forms as function arguments
On Wednesday 26 November 2003 15:40, Andreas Tille wrote: > Hi, > > I want to write a function of the following type > >CREATE FUNCTION test ( ) > RETURNS setof MyTable > AS > 'SELECT * FROM MyTable WHERE id IN $1' > LANGUAGE 'SQL' ; Not as you've done it. You could pass in text "(1,2,3)", build your query and use EXECUTE to execute it. Alternatively, you might be able to do it with an array parameter (sorry, I don't use arrays, so I can't be sure). > I was not able to find a reference whether this is possible and if yes > how to specify the argument type and how to call this function to hand over > the list for the IN clause correctly. > > Next question. Is there any example how to return a SETOF MyTable in > a plpgsql function? I tried > > CREATE FUNCTION test2() > RETURNS setof MyTable > AS ' > DECLARE >result SETOF MyTable ; > BEGIN > result := (SELECT * FROM MyTable); > RETURN result ; > END; ' LANGUAGE 'plpgsql'; Read the section on plpgsql in the manuals, you return results one at a time. For some examples, see http://techdocs.postgresql.org/ and look for the "Set Returning Functions" item. -- Richard Huxton Archonet Ltd ---(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] Postgres: MS Access and Tomcat
On Wednesday 26 November 2003 16:20, [EMAIL PROTECTED] wrote: > Hi All > I am using postgres database. Users of the DB include Web users thro tomcat > and internal user thr'o MS Access. The problem is the users of Access > sometimes do not close these connections and postgres fails saying too > many connections for the web users. Is there a way i can resolve this by > uncommenting the #Max_connections=32 in the postgresql.conf. What are the > effects of uncommenting this part in postgresql.conf. Do i need to make > any more changes when uncomment the Max_connections=32 in the > postgresql.conf file You can set max_connections higher, and unless your shared-buffer settings are very low that should be fine. Where you may encounter problems is if the Access users have open transactions which can end up blocking other clients. I seem to recall a timeout option in the ODBC manager that should disconnect them after a certain time. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem: Postgresql not starting
Hi, Am 11/26/03 16:29 schrieb [EMAIL PROTECTED]: Hi All I am using postgresql 7.2 on Linux. It does not start when i am trying this /etc/rc.d/init.d/postgresql restart. It give an error saying "postmaster already running". I have done rm -f /var/lib/pgsql/data/postmaster.pid and rm -f /var/run/postmaster.pid Try to find out, if the postmaster if really up by doing something like ps -e | grep postmaster. Then you may want to take a look in the script /etc/init.d/postgresql. Where is the PID-File actually placed ? It looks like you are trying to remove the PID-File without really knowing where it is supposed to be placed ?! The next option you have is to take a look at some logfiles (e.g var/log/messages or something depending on your settings). Greetings, Martin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
