[Pdx-pm] (OT) SQL style question

[David Wheeler]

On Jan 13, 2005, at 2:15 PM, Austin Schutz wrote:

> 	This scares me. I'm not sure if it's portable, but it will
> _definitely_ bite you when someone forgets the "s.

I believe that double-quotes are the SQL-standard way of using 
non-standard character in entity names in a database. So it should be 
portable.

> I've never even
> seen this done before.. what happens when you join something with the
> view and need to specify a column? SELECT some_view."other_table.id" ?
> Scary.

Yes, that's exactly what you do.

> 	I don't know of any alternatives, but I'd be interested to know
> if there were.

Me, too! I know that there are other characters you can use, but your 
options are limited. Allowed characters generally only allow a-zA-Z_ 
(I'd kill to be able to use "-", but it's not to be). But there is 
variation. PostgreSQL, for example, allows you to use "$". But this 
would be confusing to me:

SELECT id,
        first_name,
        last_name,
        other_table$id,
        other_table$name
FROM   some_view

...especially since $ means something in both Perl and PostgreSQL 
functions. PostgreSQL also allows the use of Unicode characters over a 
certain number. So, for example, I could do:

SELECT id,
        first_name,
        last_name,
        other_table•id,
        other_table•name
FROM   some_view

But the trouble with these solutions is that neither of them is very 
portable. :-(

I kind of like the dot notation, as it means exactly what it 
represents: A reference to another column in another table.

Regards,

David
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2369 bytes
Desc: not available
Url : http://mail.pm.org/pipermail/pdx-pm-list/attachments/20050113/b236eb8b/smime.bin