Date interval and bitmask for days - Pl/Perl or SQL - Mailing list pgsql-novice

From Ruzsinszky Attila
Subject Date interval and bitmask for days - Pl/Perl or SQL
Date
Msg-id f637dd860908310218r49d0f9h33144ee1d9cbbeff@mail.gmail.com
Whole thread Raw
List 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 by date:

Previous
From: ekekakos
Date:
Subject: Re: Triggers and Domains
Next
From: A B
Date:
Subject: How to update an entire table by getting the values from another table?