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.
SELECT ... CASE WHEN date < CURRENT_DATE THEN 'green'::text WHEN date > CURRENT_DATE THEN 'red'::text ELSE 'blue'::text END AS state, (t1.field2 || t2.fieldA) AS stuff FROM ...
BOTH state and stuff will be only available for SELECTs on the view i.e. they are not updatable ..
All views in PG are read-only. If you want to make the view updatable, you'll need to write your own rules (see manuals for details).
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
