Skip site navigation (1) Skip section navigation (2)

Re: Difficulties Storing Case Sensitive DDL Definitions

From: Martin Foster <martin(at)ethereal-realms(dot)org>
To: Lane Van Ingen <lvaningen(at)esncc(dot)com>,PostgreSQL Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Difficulties Storing Case Sensitive DDL Definitions
Date: 2005-11-15 21:57:37
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-11-15 23:45:56
Subject: createdb Question
Previous:From: Sean DavisDate: 2005-11-15 21:54:28
Subject: Re: Difficulties Storing Case Sensitive DDL Definitions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group