[SQL] Intersection of two date interval
Hi, How can i find the number of days in the intersection of 2 date interval? For example: 1st interval: (10.01.2007, 20.01.2007) 2nd interval: (13.01.2007, 21.01.2007) The intersection dates are: 13,14,15,16,17,18,19, 20 The result is: 8 How can i find the result, 8 in an sql query without using CASE statements? Table columns are id PK, date1 date, date2 date. Thanks for all your help. Suha
Re: [SQL] Intersection of two date interval
am Fri, dem 26.01.2007, um 11:39:03 +0200 mailte Suha Onay folgendes:
Hi,
How can i find the number of days in the intersection of 2 date interval?
For example:
1st interval: (10.01.2007, 20.01.2007)
2nd interval: (13.01.2007, 21.01.2007)
The intersection dates are: 13,14,15,16,17,18,19, 20
The result is: 8
How can i find the result, 8 in an sql query without using CASE statements?
Some time ago i wrote a function for this, i hope it's helpfull for you:
(not realy tested, be careful!)
create type start_end as (t1 timestamptz, t2 timestamptz);
create or replace function time_intersect (timestamptz, timestamptz,
timestamptz, timestamptz) returns start_end as $$
declare
_s1 alias for $1;
_e1 alias for $2;
_s2 alias for $3;
_e2 alias for $4;
_start timestamptz;
_endtimestamptz;
_return start_end;
begin
if _s1 _s2 then
_start := _s2;
else
_start := _s1;
end if;
if _e1 _e2 then
_end := _e1;
else
_end := _e2;
end if;
if _start _end then
_return.t1 := _start;
_return.t2 := _end;
else
_return.t1 := NULL;
_return.t2 := NULL;
end if;
return _return;
end
$$ language plpgsql;
test=# set datestyle=german;
SET
test=*# select time_intersect('10.01.2007'::date, '20.01.2007'::date,
'13.01.2007'::date, '21.01.2007'::date);
time_intersect
---
(13.01.2007 00:00:00 CET,20.01.2007 00:00:00 CET)
(1 row)
test=*# select *, t2-t1 from time_intersect('10.01.2007'::date,
'20.01.2007'::date, '13.01.2007'::date, '21.01.2007'::date);
t1| t2| ?column?
-+-+--
13.01.2007 00:00:00 CET | 20.01.2007 00:00:00 CET | @ 7 days
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 1: 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] Intersection of two date interval
Στις Παρασκευή 26 Ιανουάριος 2007 11:50, ο/η A. Kretschmer έγραψε:
am Fri, dem 26.01.2007, um 11:39:03 +0200 mailte Suha Onay folgendes:
Hi,
How can i find the number of days in the intersection of 2 date interval?
For example:
1st interval: (10.01.2007, 20.01.2007)
2nd interval: (13.01.2007, 21.01.2007)
The intersection dates are: 13,14,15,16,17,18,19, 20
The result is: 8
How can i find the result, 8 in an sql query without using CASE
statements?
Some time ago i wrote a function for this, i hope it's helpfull for you:
(not realy tested, be careful!)
create type start_end as (t1 timestamptz, t2 timestamptz);
create or replace function time_intersect (timestamptz, timestamptz,
timestamptz, timestamptz) returns start_end as $$ declare
_s1 alias for $1;
_e1 alias for $2;
_s2 alias for $3;
_e2 alias for $4;
_start timestamptz;
_endtimestamptz;
_return start_end;
begin
if _s1 _s2 then
_start := _s2;
else
_start := _s1;
end if;
if _e1 _e2 then
_end := _e1;
else
_end := _e2;
end if;
if _start _end then
_return.t1 := _start;
_return.t2 := _end;
else
_return.t1 := NULL;
_return.t2 := NULL;
end if;
return _return;
end
$$ language plpgsql;
Suha,
the function is the number of days in the
maximum of the two start dates , untill , minimum of the two end dates
interval.
But in postgresql (7.4.15 at least) there is no MIN(date,date),MAX(date,date)
functions. So someone has to write them, so you cant avoid some logic there.
Whats your problem with CASE statements?
what you are basically looking for is smth like the above implementation
from Andreas.
test=# set datestyle=german;
SET
test=*# select time_intersect('10.01.2007'::date, '20.01.2007'::date,
'13.01.2007'::date, '21.01.2007'::date); time_intersect
---
(13.01.2007 00:00:00 CET,20.01.2007 00:00:00 CET)
(1 row)
test=*# select *, t2-t1 from time_intersect('10.01.2007'::date,
'20.01.2007'::date, '13.01.2007'::date, '21.01.2007'::date); t1
| t2| ?column?
-+-+--
13.01.2007 00:00:00 CET | 20.01.2007 00:00:00 CET | @ 7 days
(1 row)
Andreas
--
Achilleas Mantzios
---(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
[SQL] implementing (something like) UNIQUE constraint using PL/pgSQL
Hello, in our application we need to implement a constraint that enforces 'at most N rows with this value', that is we have a table with 'flag' column and for each value there should be at most 10 rows (for example, the exact number does not matter). I'm trying to implement a PL/pgSQL trigger to enforce this constraint, but I'm not sure my solution is 'the best one possible'. The first solution I came up with is this: = CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$ DECLARE v_cnt INTEGER; p_cnt INTEGER; BEGIN IF TG_NARGS = 1 THEN p_cnt := TG_ARGV[0]::integer; ELSE p_cnt := 1; END IF; SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag; IF v_cnt p_cnt THEN RAISE EXCEPTION 'Too many rows with this flag!' END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE at_most(10); = But that obviously does not work as two sessions can reach the SELECT INTO statement at the same time (or until one of them commits). Thus there could be more than 'cnt' rows with the same value. Then I used a 'FOR UPDATE' lock on a separate 'lock table' that already contains all possible values of 'flag' (in reality the trigger tries to insert that value and catches the 'duplicate key' exception but that's not important here). The trigger is then = CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$ DECLARE v_cnt INTEGER; p_cnt INTEGER; BEGIN IF TG_NARGS = 1 THEN p_cnt := TG_ARGV[0]::integer; ELSE p_cnt := 1; END IF; PERFORM flag FROM lock_table WHERE flag = NEW.flag FOR UPDATE; SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag; IF v_cnt p_cnt THEN RAISE EXCEPTION 'Too many rows with this flag!'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE at_most(10); = This works (af far as I know), but I'm not sure it's the best possible solution - for example I'd like to remove the lock table. Is there some 'more elegant' way to do this? Tomas PS: Is there some up to date 'best practices' book related to PL/pgSQL? All books I've found on Amazon are pretty old (about 5 years) or are related to 'beginners' or different areas of development (PHP, etc.) ---(end of broadcast)--- TIP 1: 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] Using Temporary Tables in postgres functions
On Thu, 2007-01-25 at 08:03 -0800, Stephan Szabo wrote: On Thu, 25 Jan 2007, Mario Splivalo wrote: When I try to use TEMPORARY TABLE within postgres functions (using 'sql' as a function language), I can't because postgres can't find that temporary table. Consider this example: CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$ CREATE TEMPORARY TABLE tmpTbl AS SELECT message_id FROM cached_messages WHERE billing_status = 2; UPDATE cached_messages SET billing_status = 1 WHERE message_id IN (SELECT message_id FROM tmpTbl); SELECT * FROM v_messages_full WHERE message_id IN (SELECT message_id FROM tmpTbl); $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; It seems like the sql function checker is unhappy with the above. Does it actually work if you turn off the check_function_bodies configuration variable, create the function and then call it? Yes, when I do 'set check_function_bodies to false;' then I can create the function but I'm unable to execute it: testdb1=# select * from func1(); ERROR: relation tmptbl does not exist CONTEXT: SQL function func1 during startup Bummer. In the end I wrote the function using plpgsql. Now, is there any performance impact on using plpgsql instead of sql in simple scenarios as in func1() example? I guess there should be some, as minimas as it can be, but have no way of actualy knowing that. Mike ---(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
Re: [SQL] Using Temporary Tables in postgres functions
On Thu, 2007-01-25 at 11:00 -0500, Andrew Sullivan wrote: On Thu, Jan 25, 2007 at 03:39:14PM +0100, Mario Splivalo wrote: When I try to use TEMPORARY TABLE within postgres functions (using 'sql' as a function language), I can't because postgres can't find that temporary table. Consider this example: You need to build the temp table and EXECUTE the statement. The problem is that the plan is cached for later re-use. Since the cached plan has the id of a table that no longer exists, you get this error. See the PL/pgSQL part of the manual for more on this. Yes, I'm well aware of that, and learned to live with it :) The only drawback is that my plpgsql code looks realy uqly, because of the creation of the string variables containing the actuall SQL code that deals with the temporary tables used. Therefore I'm trying not to use temp tables as much as I can. A table-type variables would be realy nice thing to have, I guess they would exist only in memory, and for some complicated OLTP stuff those could be realy realy hand. Just a wish, in a way :) Mike ---(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
Re: [SQL] Using Temporary Tables in postgres functions
On Thu, 2007-01-25 at 11:09 -0500, Tom Lane wrote: Mario Splivalo [EMAIL PROTECTED] writes: Am I doing something wrong here, or there is no way of using temporary tables within 'sql' written functions? I believe the problem is that for a SQL function we parse the whole function body before executing any of it. So you'd need to split this into two separate functions. Having two function complicates, a bit, interface between applicaation and the database. But, then again, If I split it in two functions, then one with the UPDATE still can't find temp table referenced in other function, right? I have no problem writing func1() example in plpgsql, it just seemed to me that using sql instead of plpgsql (when I actually can use sql) gives me a little performance improvement. Mike ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Evaluate a variable
Hi, Is there anyway to evaluate a variable in plpgsql, like eval on PHP? Suppose the example: my_var:=''some value!''; a:=''my_var''; b:=eval!! a; I already tried b:=EXECUTE a; without luck! Best regards, Luís Sousa ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] LEFT Join Question
On Thu, Jan 25, 2007 at 06:51:34PM -0500, Rob V wrote: I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. I haven't tested this to remind myself for sure that it will work, but I think you ought to be able to RIGHT OUTER JOIN the table you just LEFT JOINed to to the next table using a different column. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(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
Re: [SQL] Evaluate a variable
Luís Sousa wrote: Hi, Is there anyway to evaluate a variable in plpgsql, like eval on PHP? Suppose the example: my_var:=''some value!''; a:=''my_var''; b:=eval!! a; I already tried b:=EXECUTE a; without luck! Maybe stashing a SELECT in front? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] LEFT Join Question
Thanks codeWarrior - you got me 99% there - I just needed to add the NULL trick on the join w/ the contact_phone and contact_address tables and that got me the results I was after! This is what I the final qry looks like : SELECT A.account_id, A.account_username, V.vendor_contract_signed_date, CE.contact_email, CN.contact_name_first, CA.contact_address_1, CP.contact_phone FROM account A LEFT JOIN contact_phone CP ON (CP.account_id = A.account_id OR CP.account_id IS NULL) LEFT JOIN contact_address CA ON (CA.account_id = A.account_id OR CA.account_id IS NULL) JOIN vendor V ON (V.vendor_id = A.account_id) JOIN contact_email CE ON (CE.account_id = A.account_id OR CE.account_idIS NULL) JOIN contact_name CN ON (CN.account_id = A.account_id) JOIN domain_type DT ON (CE.domain_type_id = DT.domain_type_id AND CN.domain_type_id = DT.domain_type_id AND CP.domain_type_id = DT.domain_type_id OR CN.domain_type_id IS NULL OR CP.domain_type_id IS NULL ) JOIN account_type AT ON (AT.account_type_id = A.account_type_id) HAVING AT.account_type_tag = 'ACCOUNT_VENDOR' AND DT.domain_type_tag = 'VENDOR_PRIMARY' Thanks for the help! =Rob On 1/25/07, codeWarrior [EMAIL PROTECTED] wrote: Fisrt -- you probably want to start by doing fully qualified JOINS and then you want to allow joins with nulls on the columns that are allowed to be empty: I am doing this sort of off the top of my head ... but the thing you need to do generally is to COMPLETELY QUALIFY all of your joins and then use the OR field IS NULL trick. That should solve your problem. SELECT A.account_id, A.account_username, V.vendor_status,CN.name, CA.address,CE.email, CP.phone FROM account A LEFT JOIN contact_phone CP ON (CP.account_id = A.account_id OR CP.account_id IS NULL) LEFT JOIN contact_address CA ON (CA.account_id = A.account_id OR CA.account_id IS NULL), JOIN vendor V ON (V.account_id = A.account_id), JOIN contact_email CE ON (CE.account_id = A.account_id OR CE.account_id IS NULL), JOIN contact_name CN ON (CN.account_id = A.account_id), JOIN domain_type DT ON (CE.domain_type_id = DT.domain_type_id ), JOIN account_type AT ON (AT.account_type_id = A..account_type_id) HAVING A.account_type_tag = 'ACCOUNT_VENDOR' AND DT.domain_type_tag = 'VENDOR_PRIMARY' Rob V [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello all, Ive been racking my brain for a few hours now and need some help, please!!! I have the following tables : account =account_id =account_type_id =account_username vendor =account_id =vendor_status contact_name =account_id =name =domain_type_id contact_address =account_id =address =domain_type_id contact_email =account_id =email =domain_type_id contact_phone =account_id =phone =domain_type_id account_type =account_type_id = account_type_tag records : 1 VENDOR 2 SELLER 3 CONTRACTOR domain_type =domain_type_id =domain_type_tag records : 1 PRIMARY 2 SECONDARY Im looking for a select that will give me all records where the account_type_tag =VENDOR and domain_type_tag = PRIMARY even if the address, email and phone are blank. Users WILL HAVE records in the following tables : account, vendor contact_name contact_email they MAY or MAYNOT have records in the following tables : contact_address contact_phone I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. Im basically looking for this : account_username vendor_status name address email phone - Rob123 ACTIVE ROB 123 Test Drive[EMAIL PROTECTED]555-1212 BILL123 ACTIVE Bill NULL [EMAIL PROTECTED] 456- Steve1234 INACTIVE Steve 654 Hill St [EMAIL PROTECTED] NULL I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. (do I still need the = in the where clause when using a left join?) SELECT account.account_id, account.account_username, vendor.vendor_status, contact_name.name, contact_address.address, contact_email.email, contact_phone.phone FROM account a LEFT JOIN contact_phone on (contact_phone.account_id = a.account_id) LEFT JOIN contact_address on (contact_address.account_id = a.account_id), vendor, contact_email, contact_name, domain_type, account_type, WHERE vendor.vendor_id = account.account_id AND contact_email.account_id = account.account_id AND contact_name.account_id = account.account_id AND account.account_type_id = account_type.account_type_id AND contact_email.domain_type_id = domain_type.domain_type_id AND contact_name.domain_type_id = domain_type.domain_type_id AND vendor.vendor_status_code_id = vendor_status_code.vendor_status_code_id
Re: [SQL] Using Temporary Tables in postgres functions
Mario Splivalo [EMAIL PROTECTED] writes: On Thu, 2007-01-25 at 11:09 -0500, Tom Lane wrote: I believe the problem is that for a SQL function we parse the whole function body before executing any of it. So you'd need to split this into two separate functions. Having two function complicates, a bit, interface between applicaation and the database. But, then again, If I split it in two functions, then one with the UPDATE still can't find temp table referenced in other function, right? It can as long as the table already exists when the function is entered. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Function returning SETOF using plpythonu
Hi, Is it possible to return rows from a function written in plpythonu using SETOF? Example: CREATE FUNCTION test_python_setof() RETURNS SETOF text AS ' records=plpy.execute(SELECT name FROM interface); return records ' LANGUAGE 'plpythonu'; With this code is returning the object from the execution: PLyResult object at 0xb703e458 Best regards, Luís Sousa ---(end of broadcast)--- TIP 1: 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] [NOVICE] Windows 2K Installation difficulties...
Neil Bibbins wrote: I'm logging in directly on the machine. The installation gets most of the way through, chokes, and rolls the whole thing back. I've tried altering permissions, but the PostgreSQL installer creates new accounts regardless with just user permissions. It's a mystery. Hmmm... On 1/25/07, *Oisin Glynn* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Neil Bibbins wrote: Hello, I'm having difficulty installing PostgreSQL 8.2 on Windows 2000. It gets most of the way through the installation and fails (I think) after trying to initialize the database. The log message is: The database cluster will be initialized with locale C. fixing permissions on existing directory C:/Program Files/PostgreSQL/8.2/data ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers/max_fsm_pages ... 400kB/2 creating configuration files ... ok creating template1 database in C:/Program Files/PostgreSQL/8.2/data/base/1 ... Bad command or file name could not write to child process: Invalid argument Initdb: removing contents of data directory C:/Program Files/PostgreSQL/8.2.data I have removed all virus software, although I hope to reinstall it after installation. (Can PostgresSQL really not run as an application with virus protection installed? Ouch...) I have tried deselecting the initialization box and initializing afterward, but this also doesn't work. Possibly I'm using the wrong command, which is one that I found from a post from Magnus several years ago. I have installed Cygwin, but this also didn't help. Can anyone assist? I've done many forum searches but nothing seems to work. I don't think I'm attempting the impossible! Thanks for any insight. Much appreciated. I am running on XP, 2k and 2k3 with various different VirusScanners without seeing issues (Symantec, AVG) Though 8.1.X not 8.2 Might you be hitting some permissions error on the folder/drive? Are you logged on remotely (RDP?) or directly on the machine? Oisin Make sure to copy the list on your responses and in general people prefer to reply at the bottom so someone who looks at this afresh can read it in order going down. Not sure if anyone else has any suggestions? Oisin ---(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
Re: [SQL] implementing (something like) UNIQUE constraint using PL/pgSQL
On Fri, Jan 26, 2007 at 10:41:26 +0100, Tomas Vondra [EMAIL PROTECTED] wrote: in our application we need to implement a constraint that enforces 'at most N rows with this value', that is we have a table with 'flag' column and for each value there should be at most 10 rows (for example, the exact number does not matter). Another approach is to add a instance number column and constrain that value to be between 1 and 10. And make value and instance number unique. You'll need to do a bit more work when inserting new rows than normal (to find a free instance number). This should be very robust against getting in a bad state. If you go with enforcing the condition with a trigger you need to be careful about simultaneous inserts and visibility. ---(end of broadcast)--- TIP 1: 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
