×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

perl DBI and PostgreSQL array columns

perl DBI and PostgreSQL array columns

perl DBI and PostgreSQL array columns

(OP)
Is there a good way to retrieve the value of array type columns as a perl list?

Here's an example:
CREATE TABLE test (
  Names VARCHAR(16)[]
};

INSERT INTO test VALUES ('{"Bob", "Dave"}');


I'd like to retrieve the row from the database and end up with a perl list containing the individual values "Bob" and "Dave".  My usual:
$sth = $dbh->prepare("SELECT Names FROM test");
$sth->execute;
$sth->bind_columns(\$names);
$sth->fetch;


results in $names containing the string '{"Bob", "Dave"}'.  I tried using the line:
$sth->bind_columns(\@names);

but you're not allowed to bind to a list, only a scalar.

Any ideas?

(I'd post in the perl forum, but array type columns seems fairly PostgreSQL specific.)

RE: perl DBI and PostgreSQL array columns

Hi rosenk,

I'm not sure this is exactly what you want, but here is the way I select and list row in my postgres table using perl,apache, and HTML.

####################################################

CREATE TABLE test (
  Names VARCHAR(16)[]
};

INSERT INTO test VALUES ('{"Bob", "Dave"}');

$sth = $dbh->prepare("SELECT Names FROM test");
$sth->execute;

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {

   ($names) = @$row;

    }

######################################################


LelandJ

Leland F. Jackson, CPA
Software - Master (TM)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: perl DBI and PostgreSQL array columns

(OP)
Leland, after you do that would $names be set to the single string '{"Bob", "Dave"}'?

What I'm hoping to end up with is an array, like:
@result = ("Bob", "Dave");

What I'm hoping not to have to do is parse the string myself, splitting on commas but needing to be concerned with the surrounding quotes (to handle names like "Smith, Bob")

Thanks for your help,
Keith

RE: perl DBI and PostgreSQL array columns

Hi rosenk,

Oops, I should have said:

#################################################

$thesprintf="<TR><TD align="center">%s</TD></TR>

   print "<html>\n";

   print "<BODY TEXT='black' bgcolor='white'>\n";

   print "<B><h3>";

   print "<TABLE bgcolor="blue" ALIGN='center' cellpadding='0' cellspacing='0' bordercolorlight="beige" bordercolordark="brown" BORDER='7' WIDTH='100%'>\n";

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {

   (@$names) = @$row;

    print sprintf "$thesprintf", @$names;

    }

##################################################

Notice I changes $names to an array @$names.  I use an html table enbedded in my perl script.  I use the html %s placeholder to print each selected row of the array.  The array will look like name1 name2 when printed normally, so if you need it differently, you will need to split it and parse it to add the double quotes, etc.
When I print it using the above html embedded in perl it looks like:

---------
| name1 |
---------
| name2 |
---------

The code undoubtly has errors, because I have never run it against perl to check systax.


LelandJ

Leland F. Jackson, CPA
Software - Master (TM)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: perl DBI and PostgreSQL array columns

Hi rosenk,

I went back and play with this some more and found a problem in my code.  It didn't hurt anything other than I wasn't able to access various element of the array.
#################################################

$thesprintf="<TR><TD align="center">%s</TD></TR>;

   print "<html>\n";

   print "<BODY TEXT='black' bgcolor='white'>\n";

   print "<B><h3>";

   print "<TABLE bgcolor="blue" ALIGN='center' cellpadding='0' cellspacing='0' bordercolorlight="beige" bordercolordark="brown" BORDER='7' WIDTH='100%'>\n";

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {

    @names = @$row;

    print @names[0];

    print sprintf "$thesprintf", @$row;

    }

##################################################

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: perl DBI and PostgreSQL array columns

(OP)
Thank you for your answer, Leland, but I just cannot seem to get it to work.  It does seem to allow me to access multiple columns of a resultset that way, but not multiple values from one of the columns (which is a PostgreSQL array.)

I'll poke at it a bit more and try to come up with a full code example.

RE: perl DBI and PostgreSQL array columns

Hi rosenk,

I'm still not exactly sure what your after.  Most of the work being done by my code takes place in the DBI.pm module.  It may be of some help for you to take a look-see.  I've never examined the DBD, DBI, modules, so I'm sure there is much available there of which I'm not aware.  The example I used was simply taken from the web or the perl DBI, or some other article/book, and was the customary way to access a postgres table to grab result sets at the time I wrote the code.  The DBI.pg module was re=written and improved to work with postgres 7.3.  I have the latest version of it, and this code still works fine for me.  I hope you figure out how to do what you want.

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)
https://www.smvfp.com
Nothing Runs Like the Fox

RE: perl DBI and PostgreSQL array columns

Hi rosenk,

I got to thinking some more about what you must be trying to do, so I pulled out my Perl Black book which follows:

###########################################################

This example shows how to use the arrow operator with an array reference; in this case, I'm accessing the first element in an array by using a reference to that array:

$arrayreference = [1, 2, 3];
print $arrayreference->[0];
1

What I'm creating here is a reference to an array of references to arrays.  (You get used to thinking like this when working with data structures in Perl.)  I can refer to the second array, [4, 5, 6], by dereferencing one level, like this:

$arrayreference = [[1, 2, 3], [4, 5, 6];
print "@{$arrayreference->[1]}";
4 5 6

To refer to one of the items in the array of arrays specifically -- that is, to treat this construct as a two-dimensional array--you dereference one more level like this:

$arrayreference = [[1, 2, 3], [4, 5, 6}};
print $arrayreference->[1]->[1};
5

Although Perl supports only one-dimensional arrays directly, an expression such as $arrayreference->[1]->[1] looks a lot like a two-dimensional array.  In fact, it would look more like a two-dimensional array if you could write that expression like $arrayreference[1][1].  In fact, you can.  Work through the examples until you understand it because arrays of arrays--that is, multidimensional arrays--are very powerful constructs.

Perl lets you omit the arrow operators mostly to let you work with arrays of arrays and make them look like multidimensional arrays, as in other languages.  To see how, check ou this example:

@array = (
   [1, 2],
   [3, 4],
(;

print $array[1][1];
4
###################################################

In perl arrays begin with [0] to reference the first element of an array, so perl arrays are zero based.

I tried this out with the array reference built by perl from the string returned from a postgresql SQL select query, and it works great.  This would allow you to access any element with the array by a row, column reference.

Hope this help.

LelandJ


P

Leland F. Jackson, CPA
Software - Master (TM)
https://www.smvfp.com
Nothing Runs Like the Fox

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close