perl DBI and PostgreSQL array columns

rosenk · Feb 15, 2003

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

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

INSERT INTO test VALUES ('{"Bob", "Dave"}');[/tt]

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:
[tt]$sth = $dbh->prepare("SELECT Names FROM test&quot

;
$sth->execute;
$sth->bind_columns(\$names);
$sth->fetch;[/tt]

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

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.)

Leland123 · Feb 15, 2003

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&quot

;
$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

rosenk · Feb 15, 2003

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:
[tt]@result = ("Bob", "Dave&quot

;[/tt]

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&quot

Thanks for your help,
Keith

Leland123 · Feb 15, 2003

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

Leland123 · Feb 15, 2003

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

rosenk · Feb 15, 2003

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.

Leland123 · Feb 15, 2003

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

Leland123 · Feb 15, 2003

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

perl DBI and PostgreSQL array columns

rosenk

Programmer

Leland123

Programmer

rosenk

Programmer

Leland123

Programmer

Leland123

Programmer

rosenk

Programmer

Leland123

Programmer

Leland123

Programmer

Similar threads

Part and Inventory Search

Sponsor