Обсуждение: view creation question
I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like
Select name, description, date from Tasks;
Select name, description, date from Issues;
Is there some way to wrap these two independent queries in a "CREATE VIEW myview AS" statement?
Select name, description, date from Tasks;
Select name, description, date from Issues;
Is there some way to wrap these two independent queries in a "CREATE VIEW myview AS" statement?
On Thu, Mar 09, 2006 at 10:55:12AM -0500, Larry White wrote: > I need a read only view that concatenates data from two tables. Basically > both tables would need a simple query like > > Select name, description, date from Tasks; > > Select name, description, date from Issues; > > Is there some way to wrap these two independent queries in a "CREATE VIEW > myview AS" statement? Use UNION or UNION ALL. http://www.postgresql.org/docs/8.1/interactive/queries-union.html -- Michael Fuhr
On Mar 10, 2006, at 0:55 , Larry White wrote: > Is there some way to wrap these two independent queries in a > "CREATE VIEW myview AS" statement? CREATE VIEW myview AS Select name, description, date from Tasks UNION Select name, description, date from Issues; Michael Glaesemann grzm myrealbox com
Larry White wrote: > I need a read only view that concatenates data from two tables. Basically > both tables would need a simple query like > > Select name, description, date from Tasks; > > Select name, description, date from Issues; > > Is there some way to wrap these two independent queries in a "CREATE VIEW > myview AS" statement? SELECT * FROM Tasks UNION ALL SELECT * FROM Issues; HTH -- Richard Huxton Archonet Ltd
In addition to the other replies (regarding UNION), are you aware that you can use inheritance in postgres to factor out common fields ? Then you could use the parent table to access the common denominator of the children. I'm not sure though if this fits your needs, just worth mentioning. See also: http://www.postgresql.org/docs/8.1/static/tutorial-inheritance.html Cheers, Csaba. On Thu, 2006-03-09 at 16:55, Larry White wrote: > I need a read only view that concatenates data from two tables. > Basically both tables would need a simple query like > > Select name, description, date from Tasks; > > Select name, description, date from Issues; > > Is there some way to wrap these two independent queries in a "CREATE > VIEW myview AS" statement? > >
Richard Huxton wrote: > Larry White wrote: > > SELECT * FROM Tasks > UNION ALL > SELECT * FROM Issues; In case you care about where a record originated from: SELECT *, 'Tasks' AS source FROM Tasks UNION ALL SELECT *, 'Issues' AS source FROM Issues; Regards, -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //