Is there a reason you cannot use the example as documented in the DBD::Pg docs? Maybe you use old versions of server or driver?

The example in the DBD::Pg docs seems to run out of the box. Below I used the example from the documentation and added an actual sql statement, to get the example running. 

sub run_cursor {

  my ($dbh) = @_;
  my $sql = "
    select 
        x                                   as column1
      , current_date - x * interval '1 day' as column2 
    from generate_series(1,25) f(x)
  ";

  $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");   # WITH HOLD is 
+not needed if AutoCommit is off
  while (1) {
    my $sth = $dbh->prepare("fetch 10 from csr");
    $sth->execute;
    last if 0 == $sth->rows;
    while (my $row = $sth->fetchrow_arrayref) {
      for(my$i=0;$i<$sth->{NUM_OF_FIELDS};$i++) {
        print $sth->{NAME_lc}->[$i], " = " , $row->[$i], "\t";
      }
      print "\n";
    }
  }
  $dbh->do("CLOSE csr");
}

[download]

This gives the following output (postgres 8.3.1, DBD::Pg 2.6.0): 

column1 = 1     column2 = 2008-04-16 00:00:00
column1 = 2     column2 = 2008-04-15 00:00:00
column1 = 3     column2 = 2008-04-14 00:00:00
column1 = 4     column2 = 2008-04-13 00:00:00
column1 = 5     column2 = 2008-04-12 00:00:00
column1 = 6     column2 = 2008-04-11 00:00:00
column1 = 7     column2 = 2008-04-10 00:00:00
column1 = 8     column2 = 2008-04-09 00:00:00
column1 = 9     column2 = 2008-04-08 00:00:00
column1 = 10    column2 = 2008-04-07 00:00:00
column1 = 11    column2 = 2008-04-06 00:00:00
column1 = 12    column2 = 2008-04-05 00:00:00
column1 = 13    column2 = 2008-04-04 00:00:00
column1 = 14    column2 = 2008-04-03 00:00:00
column1 = 15    column2 = 2008-04-02 00:00:00
column1 = 16    column2 = 2008-04-01 00:00:00
column1 = 17    column2 = 2008-03-31 00:00:00
column1 = 18    column2 = 2008-03-30 00:00:00
column1 = 19    column2 = 2008-03-29 00:00:00
column1 = 20    column2 = 2008-03-28 00:00:00
column1 = 21    column2 = 2008-03-27 00:00:00
column1 = 22    column2 = 2008-03-26 00:00:00
column1 = 23    column2 = 2008-03-25 00:00:00
column1 = 24    column2 = 2008-03-24 00:00:00
column1 = 25    column2 = 2008-03-23 00:00:00

[download]

(edit) Such a generated counter table can be used to join the aggregates of a real table against (below, against a date column; you'll have to adapt 'your_table' with your tablename/columns): 

select
  to_char(date_table.backcount,'FMDay'),
  to_char(date_table.backcount,'FMDD FMMon FMYYYY'),
  sum(case when yt is null then 0 else 1 end)

from (
    select  date_trunc('day', current_timestamp) - x * interval '1 day
+' as backcount
    from  generate_series(0, (select cast(extract(doy from cast('20081
+231' as date)) as integer))) as f(x) -- count back one year
  ) 
    as date_table

left join your_table yt on (date_trunc('day', yt.date::timestamptz) = 
+date_table.backcount)

group by date_table.backcount

order by date_table.backcount desc

[download]

In reply to Re: PostgreSQL cursors with Perl. by erix
in thread PostgreSQL cursors with Perl. by atemerev

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.