Обсуждение: 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?

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 //