Hi,
for some maintenance script, I used a line like:
| my $s = $DB->selectcol_arrayref ("SELECT Column1 FROM Table WHERE NOT
(Column2 = ANY(?::TEXT[]));", {}, ["abc", "def"]);
This worked perfectly, except when I started to add strings
with backslashes to the (Perl) array. Shortest non-working
example:
| my $DB = DBI->connect ('dbi:Pg:dbname=tim', undef, undef, { pg_enable_utf8 =>
1, PrintError => 0 }) or die (DBI->errstr ());
| my $TestOutput = $DB->selectcol_arrayref ('SELECT
LENGTH(unnest(?::TEXT[]));', {}, ['abc', 'def', 'g\hi']) or die ($DB->errstr
());
| $DB->disconnect () or die ($DB->errstr ());
| print Dumper $TestOutput;
gives:
| $VAR1 = [
| 3,
| 3,
| 5
| ];
On PostgreSQL's side, there end up two backslashes. Appa-
rently, this is due to the cast as a scalar string doesn't
get mangled.
Remembering the PG_BYTEA thing, I found PG_TEXTARRAY in
the manual. As there seems to be no way to bind_param () in
a selectcol_arrayref (), my first try was:
| my $st = $DB->prepare ('SELECT LENGTH(unnest(?));') or die ($DB->errstr ());
| $st->bind_param (1, undef, { pg_type => PG_TEXTARRAY }) or die ($DB->errstr
());
| $st->execute (['abc', 'def', 'g\hi']) or die ($DB->errstr ());
| while (my @r = $st->fetchrow ())
| { print Dumper \...@r; }
which gives:
| ERROR: function length(cstring) does not exist
| ZEILE 1: SELECT LENGTH(unnest($1));
| ^
| TIP: No function matches the given name and argument types. You might need
to add explicit type casts. at /var/tmp/escape-test.pl line 15.
So, another explicit cast:
| my $st = $DB->prepare ('SELECT LENGTH(unnest(?)::TEXT);') or die ($DB->errstr
());
which gives:
| $VAR1 = [
| 3
| ];
| $VAR1 = [
| 3
| ];
| $VAR1 = [
| 5
| ];
That was when I gave up and came here :-). Version informa-
tion: PostgreSQL 8.4.4, DBD::Pg 2.17.1, DBI 1.607,
Perl 5.10.0.
So, my question is: How do I pass an array of strings with
backslashes as a parameter successfully?
Tim
