Re: virtual fields on VIEW?

If I understand you correctly I believe this will work for you.

create view as select
t1.id, t1.date, t1.field1, t1.field2,
t2.fieldA, t2.fieldB,
-- state, stuff
case
when t1.date > current_date then 'red'
when t1.date < current_date then 'green'
else 'blue'
end as state,
t1.field2||t2.fieldA as stuff
from table1 as t1, table2 as t2
where t1.id = t2.fkID

Duane

-----Original Message-----
From: raptor(at)tvskat(dot)net [mailto:raptor(at)tvskat(dot)net]
Sent: Friday, June 18, 2004 6:14 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] virtual fields on VIEW?

hi,

I want to make the following thing :
select-based updatable VIEW, which have two more virtual-fields.
One of them is concatenation of others and the second is calculated on the
fly.
Can I do this and if yes how? can u give some example?

Here is the test bed :

table1) id, date, field1, field2
table2) id, fieldA, fieldB, fkID

now I want to make a view that is

create view as select
t1.id, t1.date, t1.field1, t1.field2,
t2.fieldA, t2.fieldB,
state, stuff
from table1 as t1, table2 as t2
where t1.id = t2.fkID

>> WHERE "state" is caluclated like this :

state = 'red' if date > today
state = 'green' if date < today
state = 'blue' unless date

>> AND 'stuff' is concatenation of t1.field2 and t2.fieldA.

>> BOTH state and stuff will be only available for SELECTs on the view i.e.
they are not updatable ..

can this be done, if yes how.

tia

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org