virtual fields on VIEW?

[raptor@tvskat.net]

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
[color=blue][color=green]
>> WHERE "state" is caluclated like this :[/color][/color]

state = 'red' if date > today
state = 'green' if date < today
state = 'blue' unless date
[color=blue][color=green]
>> AND 'stuff' is concatenation of t1.field2 and t2.fieldA.[/color][/color]
[color=blue][color=green]
>> BOTH state and stuff will be only available for SELECTs on the view i.e. they are not updatable ..[/color][/color]


can this be done, if yes how.

tia




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[CoL]

Re: virtual fields on VIEW?

hi,

raptor@tvskat.n et wrote:[color=blue]
> 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
>
>[color=green][color=darkred]
>>>WHERE "state" is caluclated like this :[/color][/color]
>
>
> state = 'red' if date > today
> state = 'green' if date < today
> state = 'blue' unless date
>
>[color=green][color=darkred]
>>>AND 'stuff' is concatenation of t1.field2 and t2.fieldA.[/color][/color]
> can this be done, if yes how.[/color]

try with case:

case when date > current_timesta mp then 'red' when date <
current_timesta mp then 'green' else 'blue' end as state,
t1.field2||t2.f ieldA as stuff

C.

[CoL]

Re: virtual fields on VIEW?

hi,

raptor@tvskat.n et wrote:[color=blue]
> 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
>
>[color=green][color=darkred]
>>>WHERE "state" is caluclated like this :[/color][/color]
>
>
> state = 'red' if date > today
> state = 'green' if date < today
> state = 'blue' unless date
>
>[color=green][color=darkred]
>>>AND 'stuff' is concatenation of t1.field2 and t2.fieldA.[/color][/color]
> can this be done, if yes how.[/color]

try with case:

case when date > current_timesta mp then 'red' when date <
current_timesta mp then 'green' else 'blue' end as state,
t1.field2||t2.f ieldA as stuff

C.

[Richard Huxton]

Re: virtual fields on VIEW?

raptor@tvskat.n et wrote:[color=blue]
> 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
>
>[color=green][color=darkred]
>>> WHERE "state" is caluclated like this :[/color][/color]
>
>
> state = 'red' if date > today state = 'green' if date < today state =
> 'blue' unless date[/color]
[color=blue][color=green][color=darkred]
>>> AND 'stuff' is concatenation of t1.field2 and t2.fieldA.[/color][/color]
>
>[/color]

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 ...
[color=blue][color=green][color=darkred]
>>> BOTH state and stuff will be only available for SELECTs on the
>>> view i.e. they are not updatable ..[/color][/color][/color]

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

[Richard Huxton]

Re: virtual fields on VIEW?

raptor@tvskat.n et wrote:[color=blue]
> 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
>
>[color=green][color=darkred]
>>> WHERE "state" is caluclated like this :[/color][/color]
>
>
> state = 'red' if date > today state = 'green' if date < today state =
> 'blue' unless date[/color]
[color=blue][color=green][color=darkred]
>>> AND 'stuff' is concatenation of t1.field2 and t2.fieldA.[/color][/color]
>
>[/color]

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 ...
[color=blue][color=green][color=darkred]
>>> BOTH state and stuff will be only available for SELECTs on the
>>> view i.e. they are not updatable ..[/color][/color][/color]

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

[Najib Abi Fadel]

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.fi eld2,'') ||coalesce(t2.f ieldA,'')) AS stuff






[color=blue]
> raptor@tvskat.n et wrote:[color=green]
> > 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
> >
> >[color=darkred]
> >>> WHERE "state" is caluclated like this :[/color]
> >
> >
> > state = 'red' if date > today state = 'green' if date < today state =
> > 'blue' unless date[/color]
>[color=green][color=darkred]
> >>> AND 'stuff' is concatenation of t1.field2 and t2.fieldA.[/color]
> >
> >[/color]
>
> 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 ...
>[color=green][color=darkred]
> >>> BOTH state and stuff will be only available for SELECTs on the
> >>> view i.e. they are not updatable ..[/color][/color]
>
> 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
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Najib Abi Fadel]

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.fi eld2,'') ||coalesce(t2.f ieldA,'')) AS stuff






[color=blue]
> raptor@tvskat.n et wrote:[color=green]
> > 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
> >
> >[color=darkred]
> >>> WHERE "state" is caluclated like this :[/color]
> >
> >
> > state = 'red' if date > today state = 'green' if date < today state =
> > 'blue' unless date[/color]
>[color=green][color=darkred]
> >>> AND 'stuff' is concatenation of t1.field2 and t2.fieldA.[/color]
> >
> >[/color]
>
> 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 ...
>[color=green][color=darkred]
> >>> BOTH state and stuff will be only available for SELECTs on the
> >>> view i.e. they are not updatable ..[/color][/color]
>
> 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
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[raptor@tvskat.net]

Re: virtual fields on VIEW?

> SELECT ...[color=blue]
> 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 ...[/color]

]- aha thanx..
[color=blue][color=green][color=darkred]
> >>> BOTH state and stuff will be only available for SELECTs on the
> >>> view i.e. they are not updatable ..[/color][/color]
>
> 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).[/color]

]- yep, i have to write RULES how updates/inserts will be propagandated( i made a quick read
of this section from the docs..)
[color=blue]
> --
> Richard Huxton
> Archonet Ltd
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[raptor@tvskat.net]

Re: virtual fields on VIEW?

> SELECT ...[color=blue]
> 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 ...[/color]

]- aha thanx..
[color=blue][color=green][color=darkred]
> >>> BOTH state and stuff will be only available for SELECTs on the
> >>> view i.e. they are not updatable ..[/color][/color]
>
> 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).[/color]

]- yep, i have to write RULES how updates/inserts will be propagandated( i made a quick read
of this section from the docs..)
[color=blue]
> --
> Richard Huxton
> Archonet Ltd
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruno Wolff III]

Re: virtual fields on VIEW?

On Fri, Jun 18, 2004 at 16:13:38 +0300,
"raptor@tvskat. net" <raptor@tvskat. net> wrote:[color=blue]
> 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?[/color]

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));

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bruno Wolff III]

Re: virtual fields on VIEW?

On Fri, Jun 18, 2004 at 16:13:38 +0300,
"raptor@tvskat. net" <raptor@tvskat. net> wrote:[color=blue]
> 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?[/color]

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));

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Florian G. Pflug]

Database corruption using 7.4.1

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Florian G. Pflug]

Database corruption using 7.4.1

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: Database corruption using 7.4.1

"Florian G. Pflug" <fgp@phlo.org > writes:[color=blue]
> ... 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).[/color]
[color=blue]
> 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.[/color]

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?
[color=blue]
> I finally fixed the problem by dumping all relevant tables "by hand", and
> restoring them into a clean install of 7.4.2.[/color]

How exactly did you do the "by hand" dump?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: Database corruption using 7.4.1

"Florian G. Pflug" <fgp@phlo.org > writes:[color=blue]
> ... 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).[/color]
[color=blue]
> 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.[/color]

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?
[color=blue]
> I finally fixed the problem by dumping all relevant tables "by hand", and
> restoring them into a clean install of 7.4.2.[/color]

How exactly did you do the "by hand" dump?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)