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@tvskat.net [mailto:raptor@tvskat.net]
Sent: Friday, June 18, 2004 6:14 AM
To: pgsql-general@postgresql.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
