Thread: How to create a virtual column
How do I create a virtaul column? A virtual column is accessible like any other column except that there is no physical column associated with it (unless it's indexed). The data for the column is derived from other columns in the table. For example, in a table people, a persons name is the concatentation if their first, middle and surnames.
On Sun, Nov 06, 2005 at 02:58:15AM +0000, Chris wrote: > How do I create a virtaul column? > > A virtual column is accessible like any other column except that there is no > physical column associated with it (unless it's indexed). The data for the > column is derived from other columns in the table. For example, in a table > people, a persons name is the concatentation if their first, middle and surnames. Sounds like a view to me... have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Chris <chris.velevitch@gmail.com> schrieb:
> How do I create a virtaul column?
>
> A virtual column is accessible like any other column except that there is no
> physical column associated with it (unless it's indexed). The data for the
> column is derived from other columns in the table. For example, in a table
> people, a persons name is the concatentation if their first, middle and surnames.
A view. Example:
test=> create table person (first varchar, middle varchar, surname varchar);
CREATE TABLE
test=> insert into person values ('First', 'Middle', 'Surname');
INSERT 934777 1
t=> create view view_person as select first || ' ' || middle || ' ' || surname from person;
CREATE VIEW
test=> select * from view_person ;
?column?
----------------------
First Middle Surname
(1 Zeile)
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer <akretschmer <at> spamfence.net> writes: > Chris <chris.velevitch <at> gmail.com> schrieb: > > > How do I create a virtaul column? > > A view. Example: Is a view completely compatible with tables? I mean, can I do everything with a view like I can do to a table, like insert, delete or update?
am 06.11.2005, um 22:54:15 +0000 mailte Chris folgendes: > Andreas Kretschmer <akretschmer <at> spamfence.net> writes: > > Chris <chris.velevitch <at> gmail.com> schrieb: > > > > > How do I create a virtaul column? > > > > A view. Example: > > Is a view completely compatible with tables? I mean, can I do everything with a > view like I can do to a table, like insert, delete or update? No, only insert. But you can also define RULEs for update and delete. http://www.postgresql.org/docs/8.0/interactive/rules-update.html#RULES-UPDATE-VIEWS HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Mon, Nov 07, 2005 at 07:05:14AM +0100, A. Kretschmer wrote: > am 06.11.2005, um 22:54:15 +0000 mailte Chris folgendes: > > Andreas Kretschmer <akretschmer <at> spamfence.net> writes: > > > Chris <chris.velevitch <at> gmail.com> schrieb: > > > > > > > How do I create a virtaul column? > > > > > > A view. Example: > > > > Is a view completely compatible with tables? I mean, can I do everything with a > > view like I can do to a table, like insert, delete or update? > > No, only insert. But you can also define RULEs for update and delete. > http://www.postgresql.org/docs/8.0/interactive/rules-update.html#RULES-UPDATE-VIEWS Actually, you can't do any data modification without defining rules, inserts included, at least not it 8.0. There's been talk about automatically creating these rules for simpler views, but AFAIK nothing's been done along those lines yet. decibel=# create table t(t text); CREATE TABLE decibel=# create view v_t as select * from t; CREATE VIEW decibel=# insert into v_t values('test'); ERROR: cannot insert into a view HINT: You need an unconditional ON INSERT DO INSTEAD rule. decibel=# select version(); version ------------------------------------------------------------------------------------------------------------------------- PostgreSQL 8.0.3 on powerpc-apple-darwin7.9.0, compiled by GCC gcc (GCC) 3.3 20030304 (Apple Computer, Inc. build 1495) (1 row) decibel=# -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461