Thread: perl dbi postgresql uppercase problem

Hi folks,

I just begin to use the perl modules to query a Postgresql database and
faced the first problems (which are probably very trivial)

1.    first, is it possible to store the query in a file and invoke its
execution from perl (\i file.sql command from     Postgresql) ?

2.    second, I want to use PERL variables in an SQL query (for example
$var='R') but if I use them in the query, the Postgresql server return the
following error: Attribute 'r' not found ...  I did not ask for 'r'
attribute but for 'R'. Does any one know how to resolve this
uppercase/lowercase problem ?

Thanks in advance,
marco

Marco Kienzle wrote:
>
> 1.    first, is it possible to store the query in a file and invoke its
> execution from perl (\i file.sql command from     Postgresql) ?

Just slurp in the textfile and $dbh->do() it.

> 2.    second, I want to use PERL variables in an SQL query (for example
> $var='R') but if I use them in the query, the Postgresql server return the
> following error: Attribute 'r' not found ...  I did not ask for 'r'
> attribute but for 'R'. Does any one know how to resolve this
> uppercase/lowercase problem ?

You're encountering case folding. PostgreSQL lower()s all non-quoted
attributes.
SELECT  FOO  FROM table;SELECT  Foo  FROM table;SELECT  foo  FROM table;SELECT "foo" FROM table;

These all use attribute "foo".
SELECT "Foo" FROM table;

This uses attribute "Foo".

Search the archives if you'd like to know standard ways people handle this
and the rational behind it. For using variables in the value side of any
query look up placeholders if you're not already familiar with them.