Hello, everybody

  I found that in postgresql, we can only use "select login_name as name from 
users;" to get an alias "name" for column "login_name", but in most other 
databases we can omit the "as", that is "select login_name name from users;".

  Now I will convert a lot of program from oracle to postgresql, there're so 
many place to be correct from "AAA BBB" to "AAA as BBB", can anybody give me 
a good method to do that? Or, if the next version of postgresql will have the 
same feature as other databases?

  Thanks a lot,
  Yours,
  mili


Mon, 14 Apr 2003 13:16:32 +0800
"milimeter@163.com" <milimeter@163.com>
Problem about pgsql's column alias
"milimeter@163.com" <milimeter@163.com> writes:
>   I found that in postgresql, we can only use "select login_name as name from 
> users;" to get an alias "name" for column "login_name", but in most other 
> databases we can omit the "as", that is "select login_name name from users;".

AS is not optional in Postgres, and will never be so unless we choose to
drop support for postfix operators, which I consider an unlikely choice.
Example: in
		SELECT foo ! bar;
is the "!" an infix operator, or is it postfix with the intended meaning
		SELECT (foo!) AS bar;

			regards, tom lane


Mon, 14 Apr 2003 01:44:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem about pgsql's column alias 
Tom Lane writes:

> AS is not optional in Postgres, and will never be so unless we choose to
> drop support for postfix operators, which I consider an unlikely choice.

Well, we could drop the support for postfix operators in aliasing clauses
that omit the AS.  In fact, I got the following to work with no further
changes:

target_el: a_expr AS ColLabel
           | c_expr IDENT  // new
           | a_expr
           | '*'

If we wanted more than IDENT, we would need to trade off some key words in
(few) other places, but it looks doable.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 14 Apr 2003 18:40:01 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] Problem about pgsql's column alias 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> AS is not optional in Postgres, and will never be so unless we choose to
>> drop support for postfix operators, which I consider an unlikely choice.

> Well, we could drop the support for postfix operators in aliasing clauses
> that omit the AS.

I think it would be a bad idea in any case; it would mask errors too
easily.  For example, you meant to say "SELECT a + b, ..." but wrote
"SELECT a b, ...".  Or how about this:
	SELECT 'foo'::character varying
Is "varying" an alias or part of the type name?

We've recently seen at least one comparable case where someone
unintentionally wrote what looked to the parser like a FROM-clause
alias, and was mystified at the behavior (I forget the details, but it
was just a week or two back).  Omitting AS in FROM-clauses is tolerable
because the possible syntax in that area is fairly restricted, but I
think the spec authors were just plain foolish to allow it in the SELECT
targetlist.

			regards, tom lane


Mon, 14 Apr 2003 13:00:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Problem about pgsql's column alias 
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Tom Lane writes:
> >> AS is not optional in Postgres, and will never be so unless we choose to
> >> drop support for postfix operators, which I consider an unlikely choice.
> 
> > Well, we could drop the support for postfix operators in aliasing clauses
> > that omit the AS.
> 
> I think it would be a bad idea in any case; it would mask errors too
> easily.  For example, you meant to say "SELECT a + b, ..." but wrote
> "SELECT a b, ...".  Or how about this:
> 	SELECT 'foo'::character varying
> Is "varying" an alias or part of the type name?

Shouldn't we be deprecating the use of :: in favor of the SQL-standard
CAST()?


-- 
Kevin Brown					      kevin@sysexperts.com


Mon, 14 Apr 2003 17:35:18 -0700
Kevin Brown <kevin@sysexperts.com>
Re: [GENERAL] Problem about pgsql's column alias
Kevin Brown <kevin@sysexperts.com> writes:
> Shouldn't we be deprecating the use of :: in favor of the SQL-standard
> CAST()?

Deprecate it all you like ... but I have no intention of giving up a
two-keystroke notation for a ten-keystroke one ...

			regards, tom lane


Tue, 15 Apr 2003 01:49:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Problem about pgsql's column alias 
Tom Lane writes:

> I think it would be a bad idea in any case; it would mask errors too
> easily.  For example, you meant to say "SELECT a + b, ..." but wrote
> "SELECT a b, ...".  Or how about this:
> 	SELECT 'foo'::character varying
> Is "varying" an alias or part of the type name?

That seems like a rather poor excuse for not supporting a standard feature
that people seem to ask about every week.  If someone can't tell the
difference between a and a + b he's going to have more problems than this.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 15 Apr 2003 15:24:11 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] Problem about pgsql's column alias 
jdbc and I suspect odbc have a requirement to return the table, and
schema of a column in a result set.

for jdbc at least a possible solution is to return

schema.table.column for each column returned. This could be turned
off/on using  GUC variable.

functions, and computed fields can just return the column name as they
don't belong to a schema or table.

Does anyone have any other requirments?
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


15 Apr 2003 09:38:46 -0400
Dave Cramer <dave@fastcrypt.com>
qualified column names
jdbc and I suspect odbc have a requirement to return the table, and
schema of a column in a result set.

for jdbc at least a possible solution is to return

schema.table.column for each column returned. This could be turned
off/on using  GUC variable.

functions, and computed fields can just return the column name as they
don't belong to a schema or table.

Does anyone have any other requirments?
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


15 Apr 2003 09:40:12 -0400
Dave Cramer <dave@fastcrypt.com>
qualified column names
jdbc and I suspect odbc have a requirement to return the table, and
schema of a column in a result set.

for jdbc at least a possible solution is to return

schema.table.column for each column returned. This could be turned
off/on using  GUC variable.

functions, and computed fields can just return the column name as they
don't belong to a schema or table.

Does anyone have any other requirments?
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting
-- 
Dave Cramer <Dave@micro-automation.net>


15 Apr 2003 09:40:29 -0400
Dave Cramer <Dave@micro-automation.net>
qualified column names
Peter Eisentraut <peter_e@gmx.net> writes:
> That seems like a rather poor excuse for not supporting a standard feature
> that people seem to ask about every week.

Actually, that was the first complaint that I can recall hearing in
quite some time.

I think this is simply a place where the spec got it wrong, and we
should agree to disagree, as we have done with identifier case-folding
for example ...

			regards, tom lane


Tue, 15 Apr 2003 10:17:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Problem about pgsql's column alias 
Tom Lane writes:

> I think this is simply a place where the spec got it wrong, and we
> should agree to disagree, as we have done with identifier case-folding
> for example ...

That is not at all the same situation.  The identifier-case folding goes
back to an unconscious design decision years ago and changing it now would
break everything.  Allowing column aliases without AS would break nothing.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 16 Apr 2003 15:46:36 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] Problem about pgsql's column alias 
Peter Eisentraut <peter_e@gmx.net> writes:
> Allowing column aliases without AS would break nothing.

... except postfix operators, and whatever else happens to get caught in
the crossfire.  And we'll still get complaints because it only works for
identifiers, not for keywords.

Now I'll admit that the postfix-operator issue is probably a red
herring, since "SELECT a + b, ..." should get interpreted as infix +
in any case.  But I think there are other side-effects we have not
fully analyzed yet.  The one-line grammar hack was not intended to
be actually applied, right?  Let's see a complete proposal.

			regards, tom lane


Wed, 16 Apr 2003 10:16:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Problem about pgsql's column alias 
I didn't get a reply to this, I'm wondering if it just slipped through the cracks?


jdbc and I suspect odbc have a requirement to return the table, and
schema of a column in a result set.

for jdbc at least a possible solution is to return

schema.table.column for each column returned. This could be turned
off/on using  GUC variable.

functions, and computed fields can just return the column name as they
don't belong to a schema or table.

Does anyone have any other requirments?
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


17 Apr 2003 11:19:26 -0400
Dave Cramer <dave@fastcrypt.com>
qualified column names
Tom Lane writes:

> > Allowing column aliases without AS would break nothing.
>
> Now I'll admit that the postfix-operator issue is probably a red
> herring, since "SELECT a + b, ..." should get interpreted as infix +
> in any case.  But I think there are other side-effects we have not
> fully analyzed yet.  The one-line grammar hack was not intended to
> be actually applied, right?  Let's see a complete proposal.

I have analyzed the all the grammar conflicts that the optional AS would
cause.  The real killer were these two clauses:

expr ISNULL
expr NOTNULL

I don't know how to resolve that without making ISNULL and NOTNULL more
reserved than any other word, which seems unattractive.

(The only two other areas of conflict where postfix operators and trailing
multiword type names in ::-style type casts, which we already talked
about.)

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 15 May 2003 22:04:11 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] Problem about pgsql's column alias 