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

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

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

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

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

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

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

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

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>

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

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.

Imho it is well documented, and the answer has always been that it is not easily doable,
so people don't complain, but I guess they are none the less annoyed.

I would vote for doing the change.

Andreas

On Tue, 2003-04-15 at 10:25, Zeugswetter Andreas SB SD wrote:

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.

Imho it is well documented, and the answer has always been that it is not easily doable,
so people don't complain, but I guess they are none the less annoyed.

I would vote for doing the change.

Tom raised the issue of

SELECT 'foo'::character varying
Is "varying" an alias or part of the type name?

which I've not seen addressed except for the suggestion to deprecate the
use of :: for casting, which I would sure have reservations about.

Robert Treat

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.

Imho it is well documented, and the answer has always been that it is not easily doable,
so people don't complain, but I guess they are none the less annoyed.

I would vote for doing the change.

Tom raised the issue of

SELECT 'foo'::character varying
Is "varying" an alias or part of the type name?

which I've not seen addressed except for the suggestion to deprecate the
use of :: for casting, which I would sure have reservations about.

Yes, I would also not like to depricate the :: syntax, but "varying" is an
SQL reserved word, as are all other words of multi word types, thus I see no
unsolveable problem here (disregard reserved words as alias unless AS is given).

Andreas

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

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

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

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