Postgres: Declaration of Array Types documents how to store arrays of strings in a single field of a postgres database.
I'm able to create a table and run sample queries at a psql=# prompt using this syntax. I'm wondering though whether DBI and DBD::Pg would support this as well? If tainted data from a client browser is a concern, how do you translate:

INSERT INTO housing 
   (reg_id, nights, parking, room_size, roommate, gender)
   ( '250',
     '{"Thursday","Friday","Saturday"}',
     '{"Friday"}',
     'double',
     'Kerrie',
     ''
   );

[download]
into a bind-values protected $sql query for the DBI?

However will I handle those double-quoted strings, nested inside of single_quoted curly-braced blocks while still permitting variable interpolation for my bind parameters?

Am I the only one stumped by this one?

Surely I could build the bind param one character at a time, but how would I take advantage of the natural structure of this data @nights, @parking to populate this query? Are there any DBD::Pg gurus who could lend a hand, here please?

All help appreciated.

-- Hugh

UPDATE:

Thanks to a single clue found in SuperSearch, it would seem thatRose::DB::Pg is the answer. It has two methods, ->format_array() and ->parse_array() designed for this very purpose. I haven't had an opportunity yet to test ->parse_array(), but ->format_array is working just fine for me in this application.

Here is what seems at the moment to be working code:

#!/usr/bin/perl -w
# t/23_RegistrantNeeds.t 
use strict;
use warnings;
use diagnostics;
use Test::More tests => 6;
use Test::DatabaseRow;

my $reg = Registration::WWW::RegForm->new();
my $dbh = $reg->dbh();
local $Test::DatabaseRow::dbh = $dbh;

my %test_registrants = get_test_cases();
my @test_cases = keys %test_registrants;
my @registrant_dummy = ("Crash","D.","Dummy","PhD","chip","Fiat","whac
+k");
my @hsg_fields = ('nights', 'parking', 'room_size', 'roommate', 'gende
+r');
my ($field,$tmp,@choices);

  .  .  .  

foreach my $test_case(@test_cases) {
  my $now = `date +%r`;
  $now =~ s/\s+$// ;
  my @fields = keys %{$test_registrants{$test_case}};
  $registrant_dummy[0] = $test_case;
  $registrant_dummy[1] = $now;
  my $reg_id = $reg->insert_registrant($dbh,@registrant_dummy);

  my @hsg_values = ();
  foreach $field (@hsg_fields){
    if ( $field eq 'nights' || $field eq 'parking') {
      @choices = split / /, $test_registrants{$test_case}{$field};
      $test_registrants{$test_case}{$field} = Registration::DB->format
+_array(\@choices);
    }
    # print $test_registrants{$test_case}{$field},"\n";
    push(@hsg_values,$test_registrants{$test_case}{$field});
  }
  # foreach (@hsg_values) { print "$_ \n"; }
  my $hsg_values = \@hsg_values;
  test_insert_hsg($reg,$dbh,$reg_id,$test_case,$hsg_values);

  .  .  .  

}

  .  .  .  

sub test_insert_hsg {
  my ($reg,$dbh,$reg_id,$test_case,$hsg_values) = @_;
  $reg->insert_housing($dbh,$reg_id,$hsg_values);
  my %test_registrants = get_test_cases();

  my $nights_expected = Registration::DB->format_array(split / /,$test
+_registrants{$test_case}{nights});
  $nights_expected =~ s/"//g;

  my $parking_expected = Registration::DB->format_array(split / /,$tes
+t_registrants{$test_case}{parking});
  $parking_expected =~ s/"//g;

  row_ok( table => "housing",
          where => { '='    => { reg_id => "$reg_id" }, },
          tests => { 'eq'   => {
            nights => $nights_expected,
            parking => $parking_expected,
            room_size =>                    
              $test_registrants{$test_case}{room_size},
            roommate => 
              $test_registrants{$test_case}{roommate},
            gender => $test_registrants{$test_case}{gender},
                    },
                 },
          label => 
            "RegID $reg_id: insert new row into housing table."
         );
}

[download]
And in the module being tested, the unit in question looks like this:

sub insert_housing {
  my $self = shift;
  my $dbh = shift;
  my $reg_id = shift;
  my $hsg_values = shift;
  my($sql,$sth);

  ###l4p my $logger = 
     Log::Log4perl->get_logger('RegForm.insert_housing');
  ###l4p $logger->info("\$hsg_values is @$hsg_values.");
  my $hsg_fields = "reg_id, nights, parking, room_size, 
     roommate, gender";

  $sql = "INSERT INTO housing ($hsg_fields) 
          VALUES(?,?,?,?,?,?);";
  $sth = $dbh->prepare($sql);
  $sth->execute($reg_id,@$hsg_values);
  ###l4p $logger->info("RegID $reg_id: Just inserted data 
     into housing table.");
  return 1;
}

[download]
Perhaps I should have just made Rose::DB::Pg an additional dependency, but I only needed these two routines. They are licensed under the perl license, granting rights to use and modify, so I copied them into a module already a part of this application and in my path. I gave attribution for those two routines in Registration::DB.pm's perldoc. Please advise if that is an inappropriate interpretation of the perl artisitic license.

At any rate, hope this research and experimentation is of use to the next person who needs to do this. Thanks to SuperSerach and to John C. Siracusa, the author of Rose::DB::Pg, for figuring this out before I had to.

if( $lal && $lol ) { $life++; }

In reply to DBD::Pg, bind values and postgres array data type by hesco

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.