Whats the meaning of select attributeName(tableName) from tablename in postgresql

user_15508553 0
Viewed 76

Using Postgresql I have an apparently strange behavior that I don't understand

Assume to have a simple table

create table employee (
    number int primary key,
    surname varchar(20) not null,
    name varchar(20) not null);

It is well clear for me the meaning of

select name from employee

However, I obtain all the names also with

select name(employee) from employee

and I do not understand this last statement.

I'm using PostgreSQL 13 and pgadmin 4

postgresql
2 Answers

I'd like to expand @Abelisto's answer with this quotation from PostgreSQL docs:

Another special syntactical behavior associated with composite values is that we can use functional notation for extracting a field of a composite value. The simple way to explain this is that the notations field(table) and table.field are interchangeable. For example, these queries are equivalent:

  • SELECT c.name FROM inventory_item c WHERE c.price > 1000;
  • SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;

...

This equivalence between functional notation and field notation makes it possible to use functions on composite types to implement “computed fields”. An application using the last query above wouldn't need to be directly aware that somefunc isn't a real column of the table.

user_80536550

Just an assumption.

There are two syntactic ways in PostgreSQL to call a function that receives a row as its argument. For example:

create table t(x int, y int); insert into t values(1, 2);
create function f(a t) returns int language sql as 'select a.x+a.y';
select f(t), t.f from t;
┌───┬───┐
│ f │ f │
├───┼───┤
│ 3 │ 3 │
└───┴───┘

Probably it is implemented to make the syntax same for columns also:

select f(t), t.f, x(t), t.x from t;
┌───┬───┬───┬───┐
│ f │ f │ x │ x │
├───┼───┼───┼───┤
│ 3 │ 3 │ 1 │ 1 │
└───┴───┴───┴───┘
user_5931440
Related