[postgis-users] Quoting column names in MapServer PostGIS Support

[Timothy H. Keitt]

It depends somewhat on whether you will only every access the database
via the name substitution interface, or whether you might also query the
from another program. I implemented a postgresql interface in R and
initially did some name translation (R traditionally uses "." as a
separator and I substituted them with "_"). In the end, this only
resulted in confusion. For example, you had to remember when sending a
raw query to use the actual untranslated SQL table/column names--its too
complicated to do the translations on arbitrary query strings (you'd
essentially have to write your own SQL parser). Simply quoting column
names results in the least surprise. If on the other hand, your
application has complete control over the database, and users will never
send raw queries, it might make sense to do translations.

Tim

On Wed, 2002-05-15 at 23:48, Frank Warmerdam wrote:
> Paul Ramsey wrote:
> > Frank Warmerdam wrote:
> > 
> >> This included ensuring that OGR always quotes column names (thus 
> >> resulting in odd column names like MINOR1 and LNODE# in PostgreSQL.  
> > 
> > 
> > I am going to leave most of this for Dave and Chris to address, but the 
> > above is something I would like to comment on.  Do you think it is wise 
> > to push through column names which pgsql generally considers illegal? I 
> > recognize that they can be forced with generous use of quotes, but yuck. 
> >  A little character replacement in the loader would go a long way here, 
> > me thinks...
> > 
> > "Ugly column names they are, mmmmm..... "
> 
> Paul,
> 
> I can't say that I am certain preserving them is the correct approach but
> the alternative is to be prepared to do alot of collision detetion and
> other fancy stuff.  Given that quoting column names makes it safe to use
> relatively arbitrary names, I think I would prefer to continue doing that
> as opposed to "flattening" the names for easier use in hand coded SQL.
> 
> Note that the column names aren't illegal, they are just not simple SQL
> tokens so they have to be quoted.
> 
> I am open to other feedback.
> 
> Best regards,
> 
> -- 
> ---------------------------------------+--------------------------------------
> I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
> light and sound - activate the windows | http://pobox.com/~warmerdam
> and watch the world go round - Rush    | Geospatial Programmer for Rent
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users