VIEW上的虚拟字段？ [英] virtual fields on VIEW?
问题描述
我想做以下事情：
基于选择的可更新VIEW，它还有两个虚拟字段。
其中一个是其他人的串联，第二个是动态计算的。
我可以这样做，如果是，如何？你能给出一些例子吗？
这是试验台：
table1）id，date，field1，field2
table2）id，fieldA，fieldB，fkID
现在我想创建一个视图
创建视图作为选择
t1.id，t1.date，t1.field1，t1.field2，
t2.fieldA，t2.fieldB，
州，东西
来自table1为t1，table2为t2
其中t1.id = t2.fkID
在哪里状态如下所示：
state =''red''如果日期>今天
state =''green''如果日期<今天
state =''blue''除非日期
AND''stuff''是t1.field2和t2.fieldA的串联。
BOTH状态和东西只适用于视图上的SELECT，即它们不可更新..
可以这样做，如果是的话。
tia
---------------------- -----（播出结束）---------------------------
提示6：你有没有？搜索了我们的列表档案？
http://archives.postgresql.org
ra **** @ tvskat.net 写道：创建视图作为选择
t1.id，t1.date，t1.field1，t1。 field2，
t2.fieldA，t2.fieldB，
状态，东西从table1作为t1，table2作为t2
其中t1.id = t2.fkID
< br>WHERE" state"像这样被计算：
state =''red''如果日期>今天
state =''green''如果日期<今天
state =''blue''除非日期
AND''stuff''是t1.field2和t2.fieldA的串联。
可以这样做，如果是的话。
试用大小写：
大小写时的情况> current_timestamp然后''red''当日期<
current_timestamp然后''绿色''否则''蓝色''结束状态，
t1.field2 || t2 .fieldA as stuff
C.
ra **** @ tvskat.net 写道：创建视图作为选择
t1.id，t1.date， t1.field1，t1.field2，
t2.fieldA，t2.fieldB，
状态，东西从table1作为t1，table2作为t2
其中t1.id = t2。 fkID
WHERE" state"像这样被计算：
state =''red''如果日期>今天
state =''green''如果日期<今天
state =''blue''除非日期
AND''stuff''是t1.field2和t2.fieldA的串联。
可以这样做，如果是的话。
试用大小写：
大小写时的情况> current_timestamp然后''red''当日期<
current_timestamp然后''绿色''否则''蓝色''结束状态，
t1.field2 || t2 .fieldA as stuff
C.
ra **** @ tvskat.net 写道：
我想做以下事情：基于选择的可更新VIEW，
还有两个虚拟字段。其中一个是其他人的连接，第二个是动态计算。我可以这样做，如果
是的如何？你能给出一些例子吗？
这是试验台：
table1）id，date，field1，field2 table2）id，fieldA，fieldB，fkID
现在我想创建一个视图
创建视图选择t1.id，t1.date，t1.field1，t1.field2，
t2.fieldA，t2 .fieldB，state，table1中的东西为t1，table2为t2
其中t1.id = t2.fkID
在哪里状态像这样被计算：
state =''red''如果日期>今天state =''green''如果日期<今天state =
''blue''除非日期
AND''stuff''是t1.field2和t2.fieldA的串联。
SELECT ...
CASE
WHEN date< CURRENT_DATE那么''green'':: text
WHEN date> CURRENT_DATE然后''red'':: text
ELSE''blue'':: text
END
AS州，
（t1.field2 || t2.fieldA）AS的东西
FROM ...
BOTH状态和东西只适用于
视图上的SELECT，即它们不可更新..
PG中的所有视图都是只读的。如果你想让视图更新，
你需要编写自己的规则（详见手册）。
-
Richard Huxton
Archonet Ltd
-------------------- -------（广播结束）---------------------------
提示5：您是否查看了我们广泛的常见问题解答？
http ：//www.postgresql.org/docs/faqs/FAQ.html
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. BOTH state and stuff will be only available for SELECTs on the view i.e. they are not updatable ..
can this be done, if yes how.
tia
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
hi,
ra****@tvskat.net wrote: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.fkIDWHERE "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.
can this be done, if yes how.
try with case:
case when date > current_timestamp then ''red'' when date <
current_timestamp then ''green'' else ''blue'' end as state,
t1.field2||t2.fieldA as stuff
C.
hi,
ra****@tvskat.net wrote: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.fkIDWHERE "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.
can this be done, if yes how.
try with case:
case when date > current_timestamp then ''red'' when date <
current_timestamp then ''green'' else ''blue'' end as state,
t1.field2||t2.fieldA as stuff
C.
ra****@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.fkIDWHERE "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
这篇关于VIEW上的虚拟字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
