Re: Difficulties Storing Case Sensitive DDL Definitions

Lane Van Ingen wrote:
> We are in the process of converting a legacy application to PostgreSQL,
> using Windows 2003, version 8.0.1.
>
> We have been noticing (via pgAdmin) that when we create a view, PostgreSQL
> appears to 'flatten' all of our DDL statements to lowercase. Because the
> legacy code is messy and undocumented, and because it uses names that are a
> mixture of uppercase and lowercase, we felt it would be a better to create a
> separate schema with views of the same name as the tables we are converting,
> and use the view to return rows to the app that have the mixture of upper-
> and lowercase letters it desires.
>
> For instance, here is a field that we tried to create in a view:
> as input by our CREATE VIEW statements : updatedTime
> as stored in PostgreSQL, and viewed by pgAdmin : updatedtime
> The application is issuing a query statement that wants 'updatedTime', like
> this:
> select updatedTime from <table> ....
>
> In order to overcome this, we created our views like this:
> CREATE VIEW <view_name> AS SELECT
> updatedTime AS "updatedTime",
> :
> (etc.)
>
> When trying to query it (via pgAdmin and other tools), we found we had to
> quote the field names to avoid syntax errors, like this
> select "updatedTime" from <table> ....
> This means we have to go back and change all queries in the legacy
> application if we use this approach, which is exactly what we were hoping to
> avoid.
>
> QUESTION: Is there any way around this behaviour of 'flattening' the case of
> schema objects? Don't see any config parms or run-time options that seem to
> apply.
>
>

I had a similiar problem with my scripts using Perl. What I found is
that it makes no difference at all how the application calls the
elements if quotes are not used in the elements as everything will be
lowercased and used from there.

However the trick was to have the queries quoted so that they would
return row names as the script expected it. This allowed my
application to get the names as required and meant minor changes had to
be done to the code.

I did however have to change the code slightly to enforce how the
queries are formatted, but it was not as major as changing the database
or the way the code worked with the database. Hopefully this
workaround help at all.

Martin Foster
Creator/Designer Ethereal Realms
martin(at)ethereal-realms(dot)org