Re: SYNONYMS (again)

On Thu, Jun 23, 2011 at 2:58 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
>
> > Instead of just synonyms of columns, why don't we think about
> implementing
> > virtual columns (feature as named in other RDBMS). This is the
> ability to
> > define a column in a table which is derived using an expression
> around other
> > non-virtual columns.
>
> How do you see that working differently from what PostgreSQL can
> currently do?
>
> test=# create table line_item(id int primary key not null, quantity int
> not null, unit_price numeric(13,2));
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "line_item_pkey" for table "line_item"
> CREATE TABLE
> test=# insert into line_item values (1,15,'12.53'),(2,5,'16.23');
> INSERT 0 2
> test=# create function line_total(line_item) returns numeric(13,2)
> language sql immutable as $$ select ($1.quantity *
> $1.unit_price)::numeric(13,2);$$;
> CREATE FUNCTION
> test=# select li.id, li.line_total from line_item li;
> id | line_total
> ----+------------
> 1 | 187.95
> 2 | 81.15
> (2 rows)
>

For one, this column is not part of the table, so we can't gather statistics
on them to help the optimizer.

We can'r create primary keys on this expression.

Also, say if the query wasn't fetching all the columns and we had just the
line_total call in SELECT list, the executor has to fetch the whole row and
pass it on to the function even though the function uses only part of the
row (2 columns in this case).

Regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company