On Thu, Jun 23, 2011 at 2:58 PM, Kevin Grittner <kevin.gritt...@wicourts.gov > wrote:
> Gurjeet Singh <singh.gurj...@gmail.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
