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

If I was going the view route, there is no need to use MySQL.

But that said, views are an alternative to consider. Unfortunately I've already encountered cases where PostgreSQL chooses bad query plans, and so I need to create a temporary table then query it. That is not something that I can do in a view. I've also encountered cases where it is easier to do some simple calculations outside of the database. (Example: what is the spread from one event to another in months? The catch is that if the first event happened Jan 31, and the second one Feb 2, the spread is 1 month. That kind of date arithmetic is very messy in SQL.)

In fact both of these happened on the very data set that I'd like to be able to pull into Excel. :-(

Now there is another workaround, and that is to create stored procedures in the database and select from that. I have reasons for not doing that as well, but if I can't make an intermediate layer work then I'd seriously consider going there.

  • Comment on Re^2: Excel automatically pulling data from Perl?

Replies are listed 'Best First'.
Re^3: Excel automatically pulling data from Perl?
by jbert (Priest) on Jun 28, 2007 at 15:54 UTC
    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.

[reply]
[d/l]
      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]

In Section Seekers of Perl Wisdom