Date interval and bitmask for days - Pl/Perl or SQL
От | Ruzsinszky Attila |
---|---|
Тема | Date interval and bitmask for days - Pl/Perl or SQL |
Дата | |
Msg-id | f637dd860908310218r49d0f9h33144ee1d9cbbeff@mail.gmail.com обсуждение исходный текст |
Список | pgsql-novice |
Hi, Here is a select: SELECT vonatszam, datum_tol, datum_ig, line_id, service.train_selector, bitmask FROM service, bitmask where vonatszam='40' and datum_ig>=date('2009-07-18') and datum_tol<=date('2009-07-18') and bitmask.train_selector=service.train_selector order by datum_ig desc, datum_tol desc; The output is here: "vonatszam";"datum_tol";"datum_ig";"line_id";"train_selector";"bitmask" 40;"2008-12-14";"2009-12-12";"1,1-hatar,";"40_370092";"111111111111111....<364 bit for every day>" 40;"2009-07-17";"2009-07-22";"1,1-hatar,";"40_581810";"111111" Here is a very stupid function for this task (without bitmask processing): CREATE OR REPLACE FUNCTION act_train_selector("$bevsz" character varying, "$lineid" character varying, "$req_datum" date) RETURNS character varying AS $BODY$my($vszam, $lineid, $req_datum )=@_; $ki='1'; $rv = spi_exec_query('SELECT vonatszam, datum_tol, datum_ig, line_id, train_selector, bitmask FROM service, bitmask where vonatszam='.$vszam.' and datum_ig>='.$req_datum.' and datum_tol<='.$req_datum.' and bitmask.train_selector=service.train_selector order by datum_ig desc, datum_tol desc;', 10); $bitmask=$rv->{rows}[0]->{bitmask}; $ki=$rv->{rows}[0]->{train_selector}; #my $ki = $rv->{processed}; return $ki; $BODY$ LANGUAGE 'plperl' VOLATILE COST 100; ALTER FUNCTION act_train_selector(character varying, character varying, date) OWNER TO postgres; Input: vonatszam and a given date (usually now()). Output: train_selector. I've got logical theory selecting train_selector which is the goal: - date('$rerq_datum') - date('$datum_tol') -> index for bitmask - getting the bitmask of the relevant day -> bitmask[index] - if the bitmask is 1, then return train_selector else process next record from the $rv. My problem is: - is this logic is good? - how can I use pl/perl secured using date "differences" (no external Perl modules, like Date::)? - I know there is simple SQL command count date difference. How can I use it from my pl/perl function? (I know it is a simple select, but ... an example would be cool) - performance or efficency. Is my idea is efficient on SQL server or is it a typical client side processing? (now I don't know how many request will be) TIA, Ruzsi
В списке pgsql-novice по дате отправления: