"сложно-вложенные"

"сложно-вложенные"

Сории за разные ники, интернет только у меня, а форумом пользуются несколько человек, а точнее троя...

Free

3.5. Inheritance
Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

Let's create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you're really clever you might invent some scheme like this:

CREATE TABLE capitals (
 name       text,
 population real,
 altitude   int,    -- (in ft)
 state      char(2)
);

CREATE TABLE non_capitals (
 name       text,
 population real,
 altitude   int     -- (in ft)
);

CREATE VIEW cities AS
 SELECT name, population, altitude FROM capitals
   UNION
 SELECT name, population, altitude FROM non_capitals;
This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

A better solution is this:

CREATE TABLE cities (
 name       text,
 population real,
 altitude   int     -- (in ft)
);

CREATE TABLE capitals (
 state      char(2)
) INHERITS (cities);

In this case, a row of capitals inherits all columns (name, population, and altitude) from its parent, cities. The type of the column name is text, a native PostgreSQL type for variable length character strings. State capitals have an extra column, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables.

For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 feet:

SELECT name, altitude
 FROM cities
 WHERE altitude > 500;
which returns:

  name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa  |     1953
Madison   |      845
(3 rows)

On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude of 500 feet or higher:

SELECT name, altitude
   FROM ONLY cities
   WHERE altitude > 500;

  name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa  |     1953
(2 rows)

Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE, and DELETE — support this ONLY notation.

Note
Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness. See Section 5.8, “Inheritance” for more detail.

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

INTERSECT returns all rows that are both in the result of query1 and in the result of query2. Duplicate rows are eliminated unless INTERSECT ALL is used.

EXCEPT returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.) Again, duplicates are eliminated unless EXCEPT ALL is used.

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

Let's create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you're really clever you might invent some scheme like this:

CREATE TABLE capitals (
 name       text,
 population real,
 altitude   int,    -- (in ft)
 state      char(2)
);

CREATE TABLE non_capitals (
 name       text,
 population real,
 altitude   int     -- (in ft)
);

CREATE VIEW cities AS
 SELECT name, population, altitude FROM capitals
   UNION
 SELECT name, population, altitude FROM non_capitals;
This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

A better solution is this:

CREATE TABLE cities (
 name       text,
 population real,
 altitude   int     -- (in ft)
);

CREATE TABLE capitals (
 state      char(2)
) INHERITS (cities);

In this case, a row of capitals inherits all columns (name, population, and altitude) from its parent, cities. The type of the column name is text, a native PostgreSQL type for variable length character strings. State capitals have an extra column, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables.

For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 feet:

SELECT name, altitude
 FROM cities
 WHERE altitude > 500;
which returns:

  name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa  |     1953
Madison   |      845
(3 rows)

On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude of 500 feet or higher:

SELECT name, altitude
   FROM ONLY cities
   WHERE altitude > 500;

  name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa  |     1953
(2 rows)

Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE, and DELETE — support this ONLY notation.

Note
Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness. See Section 5.8, “Inheritance” for more detail.

SELECT
...,
ARRAY(somefield FROM sometable)
FROM
...

declare @s varchar(max);
select @s=@s + somefield + ';' from sometable
select s=left(@s, len(@s)-1)