Thread: virtual fields on VIEW?
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
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
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 >
> 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 ... ]- aha thanx.. > >>> 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). ]- yep, i have to write RULES how updates/inserts will be propagandated( i made a quick read of this section from the docs..) > -- > Richard Huxton > Archonet Ltd >
On Fri, Jun 18, 2004 at 16:13:38 +0300,
"raptor@tvskat.net" <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?
You can do this using the rule system.
Below is a dump of a test of an updatable view definition that I made with
playing with this. I don't have the original source script. The pg_dump
output is a bit verbose with constraint definitions, but it should be fine
for showing you how to make simple updatable views.
CREATE TABLE test1 (
id serial NOT NULL,
name text NOT NULL
);
CREATE TABLE test2 (
id serial NOT NULL,
name text NOT NULL
);
CREATE TABLE test3 (
id1 integer NOT NULL,
id2 integer NOT NULL
);
CREATE VIEW test4 AS
SELECT test1.name AS name1, test2.name AS name2 FROM test1, test2, test3 WHERE ((test1.id = test3.id1) AND
(test2.id= test3.id2));
ALTER TABLE ONLY test1
ADD CONSTRAINT test1_name_key UNIQUE (name);
ALTER TABLE ONLY test2
ADD CONSTRAINT test2_pkey PRIMARY KEY (id);
ALTER TABLE ONLY test2
ADD CONSTRAINT test2_name_key UNIQUE (name);
ALTER TABLE ONLY test3
ADD CONSTRAINT test3_pkey PRIMARY KEY (id1, id2);
ALTER TABLE ONLY test3
ADD CONSTRAINT "$1" FOREIGN KEY (id1) REFERENCES test1(id);
ALTER TABLE ONLY test3
ADD CONSTRAINT "$2" FOREIGN KEY (id2) REFERENCES test2(id);
CREATE RULE test4_ins AS ON INSERT TO test4 DO INSTEAD INSERT INTO test3 (id1, id2) SELECT test1.id, test2.id FROM
test1,test2 WHERE ((test1.name = new.name1) AND (test2.name = new.name2));
CREATE RULE test4_del AS ON DELETE TO test4 DO INSTEAD DELETE FROM test3 WHERE ((((test1.name = old.name1) AND
(test2.name= old.name2)) AND (test1.id = test3.id1)) AND (test2.id = test3.id2));
CREATE RULE test4_upd AS ON UPDATE TO test4 DO INSTEAD UPDATE test3 SET id1 = a1.id, id2 = a2.id FROM test1 a1, test2
a2,test1 b1, test2 b2 WHERE ((((((a1.name = new.name1) AND (a2.name = new.name2)) AND (test3.id1 = b1.id)) AND
(test3.id2= b2.id)) AND (b1.name = old.name1)) AND (b2.name = old.name2));
Hi One of our production systems was running 7.4.1 for a few months, when suddenly some queries that used a specifiy table (a cache table) started crashing the backend. A colleague of mine "fixed" the problem by simply dumping and rebuilding the affected table (That was possible since it was only a cache for the results of slow queries). About 2 weeks later, the problem reappeared - this time affecting more tables. It started to analyze the problem, and found out about the alignment-bug in 7.4.1. I upgraded to 7.4.2, and fixed the system-tables according to the 7.4.2 release-note. But this didn't really help - the "analyze table" issued after fixing the system-tables exited with an error about an invalid page header in one of our tables. Dumping the database was also impossible at that stage - some tables would cause pg_dump to either abort, or to silently block (we had it running for about 10 minutes, and it didn't output a single line in that time). I finally fixed the problem by dumping all relevant tables "by hand", and restoring them into a clean install of 7.4.2. Since that 7.4.2 release-note only talked about crashing queries due to the 7.4.1 bug, but not about data-corruption occuring, I wondered if the symptoms I have seen are related to the alignment bug in 7.4.1 or not. The affected tables where all updates very frequently, and were quite large (about a million records each). The data is comes from daily imports, which delete the old records, and insert the new ones inside a transaction. I a backup of the corrupted postgres-data, so I could do further analysis if necessary. greetings, Florian Pflug
If I understand you correctly I believe this will work for you.
create view as select
t1.id, t1.date, t1.field1, t1.field2,
t2.fieldA, t2.fieldB,
-- state, stuff
case
when t1.date > current_date then 'red'
when t1.date < current_date then 'green'
else 'blue'
end as state,
t1.field2||t2.fieldA as stuff
from table1 as t1, table2 as t2
where t1.id = t2.fkID
Duane
-----Original Message-----
From: raptor@tvskat.net [mailto:raptor@tvskat.net]
Sent: Friday, June 18, 2004 6:14 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] virtual fields on VIEW?
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?
"Florian G. Pflug" <fgp@phlo.org> writes:
> ... I upgraded to 7.4.2, and fixed the system-tables
> according to the 7.4.2 release-note. But this didn't really help - the
> "analyze table" issued after fixing the system-tables exited with an
> error about an invalid page header in one of our tables. Dumping the
> database was also impossible at that stage - some tables would cause pg_dump
> to either abort, or to silently block (we had it running for about 10
> minutes, and it didn't output a single line in that time).
> Since that 7.4.2 release-note only talked about crashing queries due to the
> 7.4.1 bug, but not about data-corruption occuring, I wondered if the
> symptoms I have seen are related to the alignment bug in 7.4.1 or not.
No, I don't think so. The alignment bug could cause the planner to
crash while retrieving statistics about a table, but it would not have
any effect on fetching the data in a table. In particular I don't
believe it could have any effect at all on a COPY command, which is what
I think you are saying was failing?
> I finally fixed the problem by dumping all relevant tables "by hand", and
> restoring them into a clean install of 7.4.2.
How exactly did you do the "by hand" dump?
regards, tom lane
> 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). | |]- yep, i have to write RULES how updates/inserts will be propagandated( i made a quick read |of this section from the docs..) ]- re: to myself... In fact I was using postgres in the past afaik 6.x versions or so (no outer joins, rule system was stillnot fully working ... it was good on paper was good but, ... now glad it has advanced so much :")).. then i got using Inter Base(FireBird). (really cool db :") ) From what I see 7.x series has made huge step forward.. probably now surpacing firebird on features side.. (they had hardtime adapting Interbase code) (no flame pls!) AFAIK firebird(interbase) and postgres are the only DB with multiversioning engines, is that true ? thanx again for your help..
hi, raptor@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.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. > 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.
On Fri, Jun 18, 2004 at 02:32:16PM -0400, Tom Lane wrote: > > Since that 7.4.2 release-note only talked about crashing queries due to the > > 7.4.1 bug, but not about data-corruption occuring, I wondered if the > > symptoms I have seen are related to the alignment bug in 7.4.1 or not. > > No, I don't think so. The alignment bug could cause the planner to > crash while retrieving statistics about a table, but it would not have > any effect on fetching the data in a table. In particular I don't > believe it could have any effect at all on a COPY command, which is what > I think you are saying was failing? Yes - but also selects on the broken tables where failing (causing the backend to crash) - that's how we noticed to problem in the first place. > > I finally fixed the problem by dumping all relevant tables "by hand", and > > restoring them into a clean install of 7.4.2. > > How exactly did you do the "by hand" dump? "by hand" was a rather stupid explaination ;-) I used the regular pg_dump app, but I dumped only the tables which contain valueable data, not the ones which could be recreated from files outside of the database. Luckily all the important tables could still be dumped. greetings, Florian Pflug
raptor wrote: > > ]- re: to myself... In fact I was using postgres in the past afaik > 6.x versions or so (no outer joins, rule system was still not fully > working ... it was good on paper was good but, ... now glad it has > advanced so much :")).. then i got using Inter Base(FireBird). > (really cool db :") ) From what I see 7.x series has made huge step > forward.. probably now surpacing firebird on features side.. (they > had hard time adapting Interbase code) (no flame pls!) Firebird is the only viable open-source alternative to PG that I can think of (which has been a viable project for any length of time). > AFAIK firebird(interbase) and postgres are the only DB with > multiversioning engines, is that true ? I think Oracle has its version. -- Richard Huxton Archonet Ltd
On Mon, 2004-06-21 at 13:34, Richard Huxton wrote: > raptor wrote: > > > > ]- re: to myself... In fact I was using postgres in the past afaik > > 6.x versions or so (no outer joins, rule system was still not fully > > working ... it was good on paper was good but, ... now glad it has > > advanced so much :")).. then i got using Inter Base(FireBird). > > (really cool db :") ) From what I see 7.x series has made huge step > > forward.. probably now surpacing firebird on features side.. (they > > had hard time adapting Interbase code) (no flame pls!) > > Firebird is the only viable open-source alternative to PG that I can > think of (which has been a viable project for any length of time). > > > AFAIK firebird(interbase) and postgres are the only DB with > > multiversioning engines, is that true ? > > I think Oracle has its version. According to the author of them, innodb tables under MySQL use MVCC as well. Now if someone could just fix the parser in MySQL to not be broken...
Richard Huxton wrote: > raptor wrote: >> AFAIK firebird(interbase) and postgres are the only DB with >> multiversioning engines, is that true ? > > > I think Oracle has its version. > Yes, Oracle has it.