Re: virtual fields on VIEW?
От | Najib Abi Fadel |
---|---|
Тема | Re: virtual fields on VIEW? |
Дата | |
Msg-id | 00f601c45546$284d9020$9d64a8c0@najib обсуждение исходный текст |
Ответ на | virtual fields on VIEW? ("raptor@tvskat.net" <raptor@tvskat.net>) |
Список | pgsql-general |
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 >
В списке pgsql-general по дате отправления: