Can column name aliases be supported?

On 08/22/12 15:19, Gauthier, Dave wrote:

I know a view can be used to alias one of them.

It can alias all of them:

create view xyz as
select *, last_name as lname, last_name as surname
from mytable;

(not the nicest version but functional)

HTH.

Bosco.

On 08/22/2012 04:19 PM, Gauthier, Dave wrote:

Here's the problem....

I have a table with a column called "last_name". I have one customer
who likes to articulate queries and updates for this using column name
"last_name" (no problem there) but another who likes to call it "lname"
and yet another who likes to call it "surname". So 3 different names
for the same physical column in the table.

I know a view can be used to alias one of them. But what about the other
2? Three views (or 2 views and the main table) is awkward. Also, you
can't modify the data through a view.

I thought of having a table with the 3 column names in there
"last_name", "lname" and "surname". The queries should work fine. As
for the updates and inserts, have a before trigger make sure they're
all synched up. But that only works if only one of the value changes,
otherwise how do you know which one to sync up to! Hmmmm.....

Does anyone know of an elegant way to do this?

Thanks in Advance

What environment lets the customer articulate queries using apparently
random choices for column names?

If the customers are seeing (in their client-side vision) their
"flavour" of column name, the same magic presenting that flavour might
then be responsible for deconvolving same?

Else some nasty per client sed scripts are in your future :)

rjs

Ooops! Hit send too fast...

On 08/22/12 15:34, Bosco Rama wrote:

On 08/22/12 15:19, Gauthier, Dave wrote:

I know a view can be used to alias one of them.

It can alias all of them:

create view xyz as
select *, last_name as lname, last_name as surname
from mytable;

(not the nicest version but functional)

... and then use an INSTEAD/ALSO rule to do the update, if needed.

Bosco.

On 08/23/2012 06:41 AM, Bosco Rama wrote:

Ooops! Hit send too fast...

On 08/22/12 15:34, Bosco Rama wrote:

On 08/22/12 15:19, Gauthier, Dave wrote:

I know a view can be used to alias one of them.

It can alias all of them:

create view xyz as
select *, last_name as lname, last_name as surname
from mytable;

(not the nicest version but functional)

... and then use an INSTEAD/ALSO rule to do the update, if needed.

... though if the user UPDATEd more then one of them, who knows what'd
happen.

I'd either (a) fire the client, or (b) define different views for
different clients, with appropriate DO INSTEAD triggers (9.1) or rules
(9.0 and below).

(a) would be awfully tempting.

--
Craig Ringer

On Thu, Aug 23, 2012 at 8:19 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I have a table with a column called "last_name". I have one customer who
likes to articulate queries and updates for this using column name
"last_name" (no problem there) but another who likes to call it "lname" and
yet another who likes to call it "surname". So 3 different names for the
same physical column in the table.

Here's an out-of-the-box suggestion.

Drop the column altogether and have a single column "name". Trying to
divide names up never works properly. Does "surname" mean family name?
Not all cultures put the family name last. Is "last_name" simply the
part of the name after the last space?

Save yourself a whole lot of trouble and just store names in single
fields. And you dodge the field naming issue at the same time!

ChrisA

On 08/23/2012 11:56 AM, Chris Angelico wrote:

On Thu, Aug 23, 2012 at 8:19 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I have a table with a column called "last_name". I have one customer who
likes to articulate queries and updates for this using column name
"last_name" (no problem there) but another who likes to call it "lname" and
yet another who likes to call it "surname". So 3 different names for the
same physical column in the table.

Here's an out-of-the-box suggestion.

Drop the column altogether and have a single column "name". Trying to
divide names up never works properly. Does "surname" mean family name?
Not all cultures put the family name last. Is "last_name" simply the
part of the name after the last space?

+1 to that, and it gets way worse:

http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

and while you're at it, read this:

http://www.joelonsoftware.com/articles/Unicode.html

--
Craig Ringer

On Thu, Aug 23, 2012 at 2:19 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 08/23/2012 11:56 AM, Chris Angelico wrote:

Here's an out-of-the-box suggestion.

Drop the column altogether and have a single column "name". Trying to
divide names up never works properly. Does "surname" mean family name?
Not all cultures put the family name last. Is "last_name" simply the
part of the name after the last space?

+1 to that, and it gets way worse:

http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

Yes, that link was posted on python-list a little while back, and
that's what I had in mind as I was writing that up. Couldn't remember
the actual link though. Thanks!

and while you're at it, read this:

http://www.joelonsoftware.com/articles/Unicode.html

Definitely. I disagree with Joel Spolsky on many things, but I agree
with that post. These days, Unicode is an absolute necessity. Our
PHP-based web site has a number of issues with Unicode input, but at
least everything that goes through the database (we use Postgres for
everything) is safe.

ChrisA

Thanks for all the replies !

The real problem has nothing to do with names. I just used that as a vehicle for articulating the problem.

The view approach for queries is workable, at least for queries. Thanks for the input on that and the idea to replicate the various aliases in the view!

I take it that duplication of the aliases as formal table columns, synched up with a before trigger is a bad idea? Is there any fruit on the end of that branch? (after looking at RULES, I expect only a single dried raisin :-))

Regarding the RULES approach, I've never used RULES before, so please bear with me a bit :-)
My stab at this using the base example...

create rule lastnamealias1
on update PEOPLE.lname
do instead update PEOPLE.last_name;

create rule lastnamealias2
on update PEOPLE.surname
do instead update PEOPLE.last_name;

does that look correct (given that last_name is the actual c olumn name)?

I too would like to shoot the clients. But as they say, the customer is always right !

Thanks Again everyone !

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Chris Angelico
Sent: Thursday, August 23, 2012 1:23 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can column name aliases be supported?

On Thu, Aug 23, 2012 at 2:19 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On 08/23/2012 11:56 AM, Chris Angelico wrote:

Here's an out-of-the-box suggestion.

Drop the column altogether and have a single column "name". Trying to
divide names up never works properly. Does "surname" mean family name?
Not all cultures put the family name last. Is "last_name" simply the
part of the name after the last space?

+1 to that, and it gets way worse:

http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-abo
ut-names/

Yes, that link was posted on python-list a little while back, and that's what I had in mind as I was writing that up. Couldn't remember the actual link though. Thanks!

and while you're at it, read this:

http://www.joelonsoftware.com/articles/Unicode.html

Definitely. I disagree with Joel Spolsky on many things, but I agree with that post. These days, Unicode is an absolute necessity. Our PHP-based web site has a number of issues with Unicode input, but at least everything that goes through the database (we use Postgres for
everything) is safe.

ChrisA

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/23/2012 09:32 PM, Gauthier, Dave wrote:

The view approach for queries is workable, at least for queries. Thanks for the input on that and the idea to replicate the various aliases in the view!

The key issue with all your approaches is whether the client can ever
`UPDATE` the view. If they can, and you duplicate the column under
multiple aliases, what happens when they `UPDATE` two of the aliases to
different values?

I too would like to shoot the clients. But as they say, the customer is always right !

Heh, I wouldn't shoot them. Fire the client means:

http://sixrevisions.com/project-management/how-to-fire-bad-clients/

--
Craig Ringer

Updateable views. This is great. I didn't know about these. Absolutely delicious !
I found a great example here... http://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/

The problem of user updating >1 alias remains, but I have no problem bouncing them if they attempt that. Maybe return a "-E- You cannot update multiple aliases of the same base value" (something like that). I'd just have to detect this circumstance, raise the message and bounce (return OLD).

Does this sound doable?

-----Original Message-----
From: Craig Ringer [mailto:ringerc@ringerc.id.au]
Sent: Thursday, August 23, 2012 9:52 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can column name aliases be supported?

On 08/23/2012 09:32 PM, Gauthier, Dave wrote:

The view approach for queries is workable, at least for queries. Thanks for the input on that and the idea to replicate the various aliases in the view!

The key issue with all your approaches is whether the client can ever `UPDATE` the view. If they can, and you duplicate the column under multiple aliases, what happens when they `UPDATE` two of the aliases to different values?

I too would like to shoot the clients. But as they say, the customer is always right !

Heh, I wouldn't shoot them. Fire the client means:

http://sixrevisions.com/project-management/how-to-fire-bad-clients/

--
Craig Ringer

On 08/23/2012 10:19 PM, Gauthier, Dave wrote:

Updateable views. This is great. I didn't know about these. Absolutely delicious !
I found a great example here... http://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/

The problem of user updating >1 alias remains, but I have no problem bouncing them if they attempt that. Maybe return a "-E- You cannot update multiple aliases of the same base value" (something like that). I'd just have to detect this circumstance, raise the message and bounce (return OLD).

Does this sound doable?

With a RULE, probably not.

With a `DO INSTEAD` view trigger - available in Pg 9.1 and above - yes.
I'd recommend using a view trigger instead of a rule if at all possible.
Rules are tricksy things and sooner or later they'll bite you.

--
Craig Ringer