Re: Plpgsql function to compute "every other Friday" - Mailing list pgsql-general

On Mon, Apr 4, 2011 at 7:12 PM, C. Bensend <benny@bennyvision.com> wrote:
>
> Hey folks,
>
>   So, I'm working on a little application to help me with my
> budget.  Yeah, there are apps out there to do it, but I'm having
> a good time learning some more too.  :)
>
>   I get paid every other Friday.  I thought, for scheduling
> purposes in this app, that I would take a stab at writing a plpgsql
> function to determine if a given date is a payday.  Here is what I
> have so far:
>
>
> CREATE OR REPLACE FUNCTION is_payday( d DATE ) RETURNS BOOLEAN AS $$
>
>        DECLARE epoch DATE;
>                days_since_epoch INTEGER;
>                mult FLOAT8;
>                ret BOOLEAN := FALSE;
>
> BEGIN
>
>        SELECT INTO epoch option_value
>                FROM options WHERE option_name = 'payroll_epoch';
>
>        SELECT INTO days_since_epoch ( SELECT CURRENT_DATE - d);
>
>        *** here's where I'm stuck ***
>
>        RETURN ret;
>
> END;
> $$ LANGUAGE plpgsql;
>
>
>   OK.  So, I have a "starting" payday (payroll_epoch) in an options
> table.  That is the first payday of the year.  I then calculate the
> number of days between that value and the date I pass to the function.
> Now I need to calculate whether this delta (how many days since
> epoch) is an even multiple of 14 days (the two weeks).
>
>   I have no idea how to do that in plpgsql.  Basically, I need to
> figure out if the date I pass to the function is a payday, and if
> it is, return TRUE.
>
>   I would very much appreciate any help with this last bit of math
> and syntax, as well as any advice on whether this is a reasonable
> way to attack the problem.  And no - this isn't a homework
> assignment.  :)
>
> Thanks folks!
>
> Benny

first, let's fix your function definition.  I would advise you to take
in both the base pay date (so we know which 'every other' to use) and
the epoch so you don't have to read it from the database in the
function.  Why do that? you can make your function immutable.

CREATE OR REPLACE FUNCTION is_payday(
  d DATE,
  base_date DATE,
  payroll_epoch INT) RETURNS BOOLEAN AS $$
  SELECT (select extract('j' from $1)::int - select extract('j' from
$2)::int) % $3 = 0;
$$ LANGUAGE sql IMMUTABLE;

By making this function sql and immutable, you give the database more
ability to inline it into queries which can make a tremendous
performance difference in some cases.  You can also index based on it
which can be useful.

By pulling out julian days, we can do simple calculation based on days
(julian day, not to be confused with julian calendar, is kinda sorta
like epoch for days.  While it doesn't really apply to this toy
example, a key thing to remember if if trying to write high
performance pl/pgsql is to separate stable/immutable, and volatile
elements.  Also, use sql, not plpgsql in trivial functions.

If you don't want to select out your option in every query, I'd advise
making an option() function which wraps the trivial select:

select is_payday(some_date, option('base_date'), option('payroll_epoch');

The 'option' function should be stable.

merlin