[SQL] Difference between two times as a numeric value in a stored procedure.
Hello List, I am writing two stored procedure which alternatively returns the dayhours and nighthours of two times. (nighthours are considered between 00:00 and 06:00). As an example here is the getdayhours function: CREATE OR REPLACE FUNCTION public.getdayhours(time, time) RETURNS interval AS 'DECLARE begintime ALIAS FOR $1; endtime ALIAS FOR $2; begindate timestamp; enddate timestamp; tmpresult interval; BEGIN IF endtime = time \'00:00\' THEN enddate := (current_date+1)+endtime; ELSE enddate := current_date+endtime; END IF; IF begintime < time \'06:00\' THEN begindate := current_date + time \'06:00\'; ELSE begindate := current_date+begintime; END IF; tmpresult := enddate-begindate; IF tmpresult<\'00:00\' THEN return \'00:00\'; ELSE return tmpresult; END IF; END;' LANGUAGE 'plpgsql' VOLATILE; The working of the functions is not the problem, but the return type is. I can't seem to find a way to substract two time values (or timestamp values) and get a numeric/float value. I always get the INTERVAL datatype. For example, in stead of 4:30 i would like 4.5 as a result. I have searched the documentation but could not find any way to substract time/timestamp values and get a numeric/float as a result. When I try to CAST the interval to a numeric or float value I get an error (cannot cast time without tz to ...). Same goes for trying to cast the beginvalues and then substract them. Does anyone have any idea how I can solve/circumvent this problem? Is there a function I can use? I don't know if it helps but I'm going to use the functions like this: SELECT workhour_id, employee_id, task_id, whdate, begintime, endtime, getdayhours(begintime,endtime), getnighthours(begintime,endtime) FROM workhour Thanks in advance. Stijn Vanroye ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] ERROR: duplicate key violates unique constraint
Need help on this problem. I've created two functions. The function should perform a few sql processes. The problem is I got different results when I call the function and when I manually run the sql command. I'm using postgresql 7.4.2. The two functions involved are CREATE TABLE sensor_signature ( sid INTEGER, rh_sign_id INTEGER, PRIMARY KEY (sid,rh_sign_id)); CREATE TABLE rule_header ( rh_sign_id INTEGER, rh_statusINTEGER, rh_actionVARCHAR(6), protoVARCHAR(10), source_ipVARCHAR(255), source_port VARCHAR(64), dest_ip VARCHAR(255), dest_portVARCHAR(64), dir_operator VARCHAR(64), category VARCHAR(64), rh_revision INTEGER, timestamptimestamp, rh_ord INTEGER, PRIMARY KEY (rh_sign_id)); And my functions are CREATE OR REPLACE FUNCTION update_sen_sig (integer, varchar) RETURNS integer AS ' DECLARE sign_id alias FOR $1; category alias FOR $2; temp_sid integer; temp_category varchar; T1Cursor refcursor; BEGIN select * into temp_category from get_root_path(category); OPEN T1Cursor FOR SELECT sid FROM conf_category WHERE category_name like temp_category and category_status=1; LOOP FETCH T1Cursor INTO temp_sid; EXIT WHEN NOT FOUND; INSERT INTO sensor_signature VALUES (temp_sid, sign_id); RAISE NOTICE '' INSERT INTO sensor_signature VALUES % % '', temp_sid,sign_id; END LOOP; CLOSE T1Cursor; --- COMMIT; RETURN 0; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION rule_header_add (integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer) RETURNS integer AS ' DECLARE var_f0 alias FOR $1;-- rh_sign_id var_f1 alias FOR $2;-- rh_status var_f2 alias FOR $3;-- rh_action var_f3 alias FOR $4;-- proto var_f4 alias FOR $5;-- source_ip var_f5 alias FOR $6;-- source_port var_f6 alias FOR $7;-- dest_ip var_f7 alias FOR $8;-- dest_port var_f8 alias FOR $9;-- dir_operator var_f9 alias FOR $10;-- category var_f10 alias FOR $11;-- rh_revision var_f11 alias FOR $12;-- rh_ord curtime timestamp; var_temp_RH_ORD integer; BEGIN curtime := current_timestamp; SELECT rh_ord INTO var_temp_RH_ORD FROM rule_header WHERE rh_sign_id = var_f0; IF NOT FOUND THEN INSERT INTO rule_header VALUES (var_f0,var_f1,var_f2,var_f3,var_f4,var_f5,var_f6,var_f7,var_f8,var_f9,var_f10,curtime,var_f11); RAISE NOTICE '' INSERT INTO rule_header VALUES % % % % % % % % % % % % '', var_f0,var_f1,var_f2,var_f3,var_f4,var_f5,var_f6,var_f7,var_f8,var_f9,var_f10,curtime,var_f11; ---select update_sen_sig(var_f0,var_f9); perform update_sen_sig(var_f0,var_f9); RETURN 0; ELSE UPDATE rule_header SET rh_status=var_f1, rh_action=var_f2, proto=var_f3,source_ip=var_f4, source_port=var_f5, dest_ip=var_f6, dest_port=var_f7, dir_operator=var_f8, category=var_f9, rh_revision=var_f10, timestamp=curtime, rh_ord=var_temp_RH_ORD where rh_sign_id=var_f0; DELETE FROM rule_option where rh_sign_id=var_f0; RETURN 1; END IF; END; ' LANGUAGE 'plpgsql'; Issue is cews=> select rule_header_add(999,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules',3,0); NOTICE: INSERT INTO rule_header VALUES 999 1 alert ip $EXTERNAL_NET any $HOME_NET any -> dos.rules 3 2004-06-04 15:21:30.448633 NOTICE: INSERT INTO sensor_signature VALUES -1 999 CONTEXT: PL/pgSQL function "rule_header_add" line 26 at perform ERROR: duplicate key violates unique constraint "sensor_signature_pkey" CONTEXT: PL/pgSQL function "update_sen_sig" line 16 at SQL statement PL/pgSQL function "rule_header_add" line 26 at perform I thought it might be caused by duplicated data. But ... cews=> insert into rule_header values (268,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules',3,current_timestamp,0); INSERT 29393 1 And cews=> insert into sensor_signature values (-1,268); INSERT 29394 1 That commands work perfectly. Could somebody tell me why and how to solve this. Thanks. Azmi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] trigger data
Does any one have any reasonably simple application example that uses or parses the HeapTuple (PointerGetDatum(rettuple) ) returned by 'c' trigger function? Also, what is the syntax to parse the HeapTuple within the trigger function? thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Difference between two times as a numeric value in a stored procedure. [Scanned]
> "Stijn Vanroye" <[EMAIL PROTECTED]> writes: > > I can't seem to find a way to substract two time values (or > > timestamp values) and get a numeric/float value. I always get the > > INTERVAL datatype. > > extract(epoch from interval) may help. > > regards, tom lane Indeed. I think I should kick myself for missing that, I have read that section af the manual four times or so, and missed it over and over again :) Thanks for the help. Regards, Stijn Vanroye. ---(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] Difference between two times as a numeric value in a stored procedure.
On Fri, Jun 04, 2004 at 10:11:57 +0200, Stijn Vanroye <[EMAIL PROTECTED]> wrote: > The working of the functions is not the problem, but the return type is. I can't > seem to find a way to substract two time values (or timestamp values) and get a > numeric/float value. I always get the INTERVAL datatype. For example, in stead of > 4:30 i would like 4.5 as a result. I have searched the documentation but could not > find any way to substract time/timestamp values and get a numeric/float as a result. > When I try to CAST the interval to a numeric or float value I get an error (cannot > cast time without tz to ...). Same goes for trying to cast the beginvalues and then > substract them. Does anyone have any idea how I can solve/circumvent this problem? > Is there a function I can use? You can use EXTRACT to extract the EPOCH from the interval which will be the number of seconds in the interval. You can then use arithmetic (/60.) on that number to get fractional hours. ---(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] dynamic rules?
Is it possible to create a select rule that returns results depending on a where clause in the original select statement? For Example (oversimplified): CREATE TABLE one (data int); CREATE TABLE two (data int); I make the query: SELECT data FROM number WHERE type=1; If the type is 1 (type=1) I want to return 'SELECT data FROM one;' If the type is 2 (type=2) I want to return 'SELECT data FROM two;' Thanks, Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] dynamic rules?
On Thu, Jun 03, 2004 at 21:32:47 -0700, Christopher Dedels <[EMAIL PROTECTED]> wrote: > Is it possible to create a select rule that returns results depending on a > where clause in the original select statement? > > For Example (oversimplified): > > CREATE TABLE one (data int); > CREATE TABLE two (data int); > > I make the query: > SELECT data FROM number WHERE type=1; > > If the type is 1 (type=1) I want to return 'SELECT data FROM one;' > If the type is 2 (type=2) I want to return 'SELECT data FROM two;' If you don't need to do updates, you can do this with a view. It would look something like: CREATE VIEW number (data, type) AS SELECT one.data, 1 FROM one UNION SELECT two.data, 2 FROM two; I didn't test the command so there might be a syntax error in it, but the approach should work. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ERROR: duplicate key violates unique constraint
Khairul, > Need help on this problem. I've created two functions. The function > should perform a few sql processes. The problem is I got different > results when I call the function and when I manually run the sql command. > > I'm using postgresql 7.4.2. Hmmm ... that's odd. I remember getting this issue early in the 7.2 series but not since. I'd guess that you're missing something in your function, like the transposition of two fields or an unterminated loop. You've made that likely because: > RETURNS integer AS ' > DECLARE > var_f0 alias FOR $1;-- rh_sign_id > var_f1 alias FOR $2;-- rh_status > var_f2 alias FOR $3;-- rh_action > var_f3 alias FOR $4;-- proto ... this is a really bad way of dealing with function variables; I certainly can't parse the rest of the function and tell if you've accidentally swapped a var_f3 for a var_f4. I'd strongly suggest naming your variables clearly, like, for example, calling it "v_proto" instead of "var_f3". This is "programming 101". > Issue is > cews=> select > rule_header_add(999,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any', >'->','dos.rules',3,0); NOTICE: INSERT INTO rule_header VALUES 999 1 alert > ip $EXTERNAL_NET any $HOME_NET any -> dos.rules 3 2004-06-04 > 15:21:30.448633 > NOTICE: INSERT INTO sensor_signature VALUES -1 999 > CONTEXT: PL/pgSQL function "rule_header_add" line 26 at perform > ERROR: duplicate key violates unique constraint "sensor_signature_pkey" > CONTEXT: PL/pgSQL function "update_sen_sig" line 16 at SQL statement > PL/pgSQL function "rule_header_add" line 26 at perform > > I thought it might be caused by duplicated data. But ... > cews=> insert into rule_header values > (268,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules >',3,current_timestamp,0); INSERT 29393 1 > > And > cews=> insert into sensor_signature values (-1,268); > INSERT 29394 1 This isn't the same id you tested with the function. Mind running the *exact same values* with both command line and function? Also, I notice that update_sen_sig makes use of a cursor and a loop. Best guess is that the cursor isn't returning what you think it is, and is looping several times ... thus attempting to insert the same value several times. Good luck! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Difference between two times as a numeric value in a stored procedure.
"Stijn Vanroye" <[EMAIL PROTECTED]> writes: > I can't seem to find a way to substract two time values (or > timestamp values) and get a numeric/float value. I always get the > INTERVAL datatype. extract(epoch from interval) may help. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] dynamic rules?
On Fri, Jun 04, 2004 at 09:16:04 -0700, Christopher Dedels <[EMAIL PROTECTED]> wrote: > Bruno, > > Thanks for the response. You should keep the discussion copied to the list as long as it is on topic. That way other people can help out. > I was wondering if I was possible to union the tables if necessary. So, if > I choose type=1 it selects from only the 'one' table and likewise with 2. > However, if I query type=1 or type=2 it will union the two and return the > results from both tables. That is a planner issue. I suspect that currently postgres won't be able to see that a union isn't necessary. However unless performance is a real problem for you, you are better off using simpler code to help maintainance. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] ANSI SQL-99 SYNTAX and "WITH"
Good morning,afternoon, and evening Everyone, My research into PostgreSQL documentation has yet to yeild any information about PostgreSQL's support of ANSI SQL-99 syntax, and in particular, the "WITH" clause - which if I understand correctly, allows recursive joins in simple syntax without the use of cursors and/or otherwise klugey SQL. As you may have guessed, I hope PostgreSQL currently supports the "WITH" clause syntax or, will at least support it imminently. Any knowledge on the matter is greatly appreciated :) Rein
Re: [SQL] ANSI SQL-99 SYNTAX and "WITH"
> Good morning,afternoon, and evening Everyone, > > My research into PostgreSQL documentation has yet to yeild any information about > PostgreSQL's support of ANSI SQL-99 syntax, and in particular, the "WITH" clause - > which if I understand correctly, allows recursive joins in simple syntax without the > use of cursors and/or otherwise klugey SQL. > > As you may have guessed, I hope PostgreSQL currently supports the "WITH" clause > syntax or, will at least support it imminently. No, PostgreSQL does not support the WITH clause. I wanted to use the WITH clause in an assignment, but I ended up using a view. Maybe that will work for you. Yasir ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ANSI SQL-99 SYNTAX and "WITH"
On Fri, Jun 04, 2004 at 15:37:41 -0400, Rein Petersen <[EMAIL PROTECTED]> wrote: > My research into PostgreSQL documentation has yet to yeild any information about > PostgreSQL's support of ANSI SQL-99 syntax, and in particular, the "WITH" clause - > which if I understand correctly, allows recursive joins in simple syntax without the > use of cursors and/or otherwise klugey SQL. > > As you may have guessed, I hope PostgreSQL currently supports the "WITH" clause > syntax or, will at least support it imminently. Postgres doesn't currently support that. Someone was working on it a while back, but I haven't heard anything lately, so I don't know if some version of it is going to be in the next release. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] ANSI SQL-99 SYNTAX and "WITH"
Bruno Wolff III <[EMAIL PROTECTED]> writes: > Rein Petersen <[EMAIL PROTECTED]> wrote: >> As you may have guessed, I hope PostgreSQL currently supports the >> "WITH" clause syntax or, will at least support it imminently. > Postgres doesn't currently support that. Someone was working on it a while > back, but I haven't heard anything lately, so I don't know if some version > of it is going to be in the next release. Andrew Overholt was working on it awhile back. I've been meaning to pick up the pieces and see what I could do with it, but at this point it's a pretty safe bet that it won't make 7.5. Maybe next time. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
