Re: virtual fields on VIEW?

NOTE THAT if field2 or fieldA might contain NULL values u should use
coalesce if u don't want to have a NULL value if one of the fields is NULL:
If field2 and fieldA are strings you will have something like that
(coalesce(t1.field2,'') ||coalesce(t2.fieldA,'')) AS stuff

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>