Date interval and bitmask for days - Pl/Perl or SQL

From: Ruzsinszky Attila <ruzsinszky(dot)attila(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Date interval and bitmask for days - Pl/Perl or SQL
Date: 2009-08-31 09:18:24
Message-ID: f637dd860908310218r49d0f9h33144ee1d9cbbeff@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 )=(at)_;
$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

Browse pgsql-novice by date

  From Date Subject
Next Message nextstopsun 2009-08-31 09:44:40 Problem starting pgsql server on Mac OS X. Pg_hba.conf reading permission.
Previous Message ekekakos 2009-08-30 18:56:00 Re: Triggers and Domains