Сохранять общий запрос в виде столбца?

Ответ 1

Есть ли способ сохранить этот подвыбор в виде псевдоколла в таблице?

A VIEW, как было рекомендовано, является совершенно правильным решением. Но есть еще один способ, который подходит вашему вопросу еще более внимательно. Вы можете написать функцию, которая берет тип таблицы как параметр для эмулировать a "вычисленное поле" или "сгенерированный столбец.

Рассмотрим этот тестовый пример, полученный из вашего описания:

CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);

CREATE TABLE tbl_b (b_id int, a_id int, colx int);
INSERT INTO tbl_b VALUES
 (1,1,5),  (2,1,5),  (3,1,1)
,(4,2,8),  (5,2,8),  (6,2,6)
,(7,3,11), (8,3,11), (9,3,11);

Создайте функцию, которая эмулирует col3:

CREATE FUNCTION col3(tbl_a)
  RETURNS int8 AS
$func$
    SELECT sum(colx)
    FROM   tbl_b b
    WHERE  b.a_id = $1.a_id
$func$ LANGUAGE SQL STABLE;

Теперь вы можете запросить:

SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

Или даже:

SELECT *, a.col3 FROM tbl_a a;

Обратите внимание, как я написал tbl_a.col3/a.col3, а не только col3. Это важно.

В отличие от "виртуального столбца" в Oracle, он не автоматически включается в SELECT * FROM tbl_a. Вы можете использовать для этого VIEW.

Почему это работает?

Общим способом ссылки на столбец таблицы является обозначение атрибута:

SELECT tbl_a.col1 FROM tbl_a;

Общим способом вызова функции является функциональная нотация:

SELECT col3(tbl_a);

Как правило, лучше придерживаться этих канонических способов, которые согласуются со стандартом SQL.

Но в PostgreSQL функциональная нотация и атрибутная нотация эквивалентны. Так что эти работы также:

SELECT col1(tbl_a) FROM tbl_a;
SELECT tbl_a.col3;

Подробнее об этом в руководстве.
Вероятно, вы видите, что это происходит. Похоже, вы добавили бы дополнительный столбец таблицы tbl_a, а col3() - фактически функция, которая принимает текущую строку tbl_a (или ее псевдоним) в качестве аргумента типа строки и вычисляет значение.

SELECT *, a.col3
FROM   tbl_a AS a;

Если существует фактический столбец col3, он имеет приоритет, и система не ищет функцию этого имени, беря в качестве параметра строку tbl_a.

Красота: вы можете добавлять или удалять столбцы из tbl_a, а последний запрос будет динамически возвращать все текущие столбцы, где представление будет возвращать только такие столбцы, которые существовали во время создания (раннее связывание или поздняя привязка *).
Конечно, перед тем, как вы отпустите таблицу, вам нужно отказаться от функции. И вы должны позаботиться о том, чтобы не делать недействительной функцию при внесении изменений в таблицу.

Ответ 2

По-видимому, это обрабатывается с представлениями, как в комментарии льва. Поэтому в моем случае я использовал команду:

CREATE VIEW <viewname> AS
SELECT *, (SELECT sum(<col x>)
FROM   <otherTable
WHERE  <otherTable foreignkeyCol>=<thisTable keycol>) AS <col 3>
FROM   <tablename>

который по существу дает мне другую таблицу, включая нужный столбец.

Ответ 3

Помимо представления, вы можете создать функцию для суммы.

CREATE FUNCTION sum_other_table( key type_of_key ) RETURNS bigint
AS $$ SELECT sum( col_x ) FROM table_1 where table_1.key = key $$ LANGUAGE SQL;

а затем используйте его как ваш агрегатор:

SELECT col_1, col_2, sum_other_table( key ) AS col_3
FROM table_2 WHERE table_2.key = key;

Обратите внимание, что возвращаемый тип sum_other_table() зависит от типа столбца, который вы суммируете.

Ответ 4

До сих пор есть три ответа, все из которых работают. Любой из них может быть "лучшим решением" в зависимости от обстоятельств. С небольшими таблицами производительность должна быть довольно близкой, но ни одна из них не может масштабироваться хорошо для таблиц с миллионами строк. Самый быстрый способ получить желаемые результаты с большим набором данных, вероятно, будет (с использованием установки Erwin):

SELECT a_id, col1, col2, sum(colx)
FROM tbl_a LEFT JOIN tbl_b b using(a_id)
GROUP BY a_id, col1, col2;

Если a_id объявлен как первичный ключ, и это выполняется под 9.1 или новее, предложение GROUP BY может быть упрощено, поскольку col1 и col2 функционально зависят от a_id.

SELECT a_id, col1, col2, sum(colx)
FROM tbl_a LEFT JOIN tbl_b b using(a_id)
GROUP BY a_id;

Этот вид можно определить таким образом, и он будет масштабироваться, но я не думаю, что для подходов, использующих функции, будут рассмотрены все те же пути выполнения, поэтому самый быстрый путь выполнения не может быть использован.