From: robert_hiltibidal_at_cms08405@ccmailgw.state.il.us
Message-Id: <9906229300.AA930073628@ccmailgw.state.il.us>
Date: Tue, 22 Jun 99 12:46:33 -0600
To: <pgsql-interfaces@postgresql.org>
Subject: Perl Interface
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit


     Afternoon,
     
     Something that's been driving me a little buggy maybe someone Out 
     There can shed some light...
     
     We use postgres for user management on linux systems. Nothing fancy 
     about the fields, just username,password,lastname,firstname etc.
     
     Traditionally I've used the shell psql function:
     @exec = `psql -A -d users -t -q -c "Select * from temp"`;
     
     Weeellll now I'm running into server timeouts on the box. The 
     adminmstration box does see a fair amount of load. We use sql 
     extensively and consequently we ahve a large number of cron jobs that 
     fire up psql shells. I had thought using the methods found in pg would 
     help. I am planning to port our applications over to C but... that's a 
     several month job. I'm looking for a quick fix here to buy me some 
     time. The other side of that is C is not my preferred language of 
     choice... I do pascal and perl.  
     
     I am not clear yet as how to navigate multiple rows on the recordset 
     return using the pg method. Ideally I want to put all the results into 
     an array like this:
     username|password|lastname
     marty|next|Robinson
     mel|foreman|Johnson
     
     The <$ret = $result->fetchrow> function only returns a portion of the 
     row. 
     
     I have created a lttle subroutine using the getvalue method that 
     iterates thru each row and pushes it into an array. This will work for 
     now. However I am curious, is there another, more simpler method to 
     accomplish this? 
     
     Thanks,
     
     -Rob
     
     ############################
     #       Subroutine         #
     ############################
     
     $query = "Select username,password,knickname,emailaddy,forward,ip,date 
     from temp";
     $conn = Pg::connectdb("dbname=$dbname");
     $result = $conn->exec($query);
     $tuples = $result->ntuples;
     $fields = $result->nfields;
     $count = 0;
     while ($count < $tuples ) {
           $fieldcount = 0;
           for ($fieldcount = 0;;$fieldcount++) {
               $entry .= $result->getvalue($count,$fieldcount);
               if ($fieldcount != $fields) {
                  $entry .= "\|";
               }    
                else {
                  last;
               }
           }
           push(@set,$entry);
           $entry="";
           $count++;
     }           
     
     for $member (@set) {
          print "$member\n";
     }             
     
     ###########################
     When run it produces this output. We need to keep this format for 
     existing scripts. 
     
     marty|marty|fghu|sdfg@fghj|t|208.130.70.9|Tue Jun 22 00:09:34 1999 CDT
     marty1|marty|fghu|sdfg@fghj|f|208.130.70.9|Tue Jun 22 00:10:18 1999 
     CDT