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

Apologies for mentioning MySQL, I'm less familiar with PostgreSQL.

Is the date manipulation issue that much more cumbersome with SQL than perl? 

select period_diff(extract(year_month from date1), extract(year_month 
+from date2)) as month_spread from foo;

[download]

I guess a big consideration for this is whether the data is allowed to be a little stale, in which case you don't need any complex technology and can simply periodically recreate tables in the db via a scheduled perl job.

If it does need to be 'on demand' (i.e. someone hitting refresh in Excel needs/wants/expects the *live* data rather than something 5 mins old) then this might be accomplished with a db trigger which shells out to run a perl script to repopulate a table. Although the HTTP route might still be better here I guess.

It also depends on data set sizes, time to run the 'report', etc.

Replies are listed 'Best First'.
Re^4: Excel automatically pulling data from Perl?
by tilly (Archbishop) on Jun 29, 2007 at 00:52 UTC
    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.)
[reply]
[d/l]
      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]

In Section Seekers of Perl Wisdom