Thread: Can column name aliases be supported?
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
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 replicatethe 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 thereany 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'tremember 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 thedatabase (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
replicatethe 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 replicatethe 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 thecolumn 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 returna "-E- You cannot update multiple aliases of the same base value" (something like that). I'd just have to detect thiscircumstance, 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