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@tvskat.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
>