Re: virtual fields on VIEW?

raptor(at)tvskat(dot)net wrote:
> 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