in reply to Re^3: Excel automatically pulling data from Perl?
in thread Excel automatically pulling data from Perl?

Um, period_diff is a MySQL extension. So is year_month. (Most useful ways of dealing with dates in databases tend to be extensions.)

After messing around with http://www.postgresql.org/docs/8.2/static/functions-datetime.html the best that I could find in PostgreSQL looks something like this: 

SELECT
    12 * extract(year from date1) + extract(month from date1)
      - 12 * extract(year from date2) - extract (month from date2)
        as month_spread
FROM foo

[download]
(I guess my Perl solution was longer, but it was more obvious code for me.)

Replies are listed 'Best First'.
Re^5: Excel automatically pulling data from Perl?
by jbert (Priest) on Jun 29, 2007 at 06:07 UTC
    Oh, thanks very much for that. I'll try and be less dialect-specific in future.

    Hmm...digging through the PostgreSQL docs, I see that it should be possible to have PG user-defined-functions in perl, via a perl plugin for PG.

    So I guess you might be able to do some appropriate CREATE FUNCTION calls to stash some perl code within your db server and then define some PG views in terms of these functions.

    Or is this adding too much complexity, especially since the HTTP solution exists?

[reply]
[d/l]
      The complexity that it adds which I really don't like is that it moves code out of a directory under revision control and into the database. Sure, if people are good about it, you can keep them in sync. But why let the issue come up if there is a choice?
[reply]
        Very good point. Of course, a cron which regularly checks the stored function from the db against a copy in the version control system and updates the VCS (and possibly complains somewhere via email) if the db copy changes is pretty easy, but is also just another thing to go wrong.

        Then again, you could argue that your db schema is as much a part of your app as such stored functions and the same applies to them. If you already have scripts to recreate your schema under version control (which some people do) then you could consider this as part of the same?

        Sorry for continuing this theme ad nauseum, I'm just intrigued by the pros and cons of this.

[reply]

In Section Seekers of Perl Wisdom