Armazenar consulta comum como coluna?

Usando o PostgreSQL, tenho várias consultas que se parecem com isso:

SELECT ,  , (SELECT sum() FROM  WHERE =) AS  FROM  

Dado que a sub-seleção será idêntica em todos os casos, existe uma maneira de armazenar essa sub-seleção como uma pseudo coluna na tabela? Essencialmente, quero poder selecionar uma coluna da tabela A que seja uma sum de uma coluna específica da tabela B na qual os registros estão relacionados. Isso é possível?

Existe uma maneira de armazenar essa sub-seleção como uma pseudo-coluna na tabela?

Uma VIEW como foi aconselhada é uma solução perfeitamente válida. Mas há outra maneira que se adapta à sua pergunta ainda mais de perto. Você pode escrever uma function que usa o tipo de tabela como parâmetro para emular um “campo computado” ou “coluna gerada” .

Considere este caso de teste, derivado da sua descrição:

 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); 

Criar function que emula 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; 

Agora você pode consultar:

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

Ou até mesmo:

 SELECT *, a.col3 FROM tbl_a a; 

Note como escrevi tbl_a.col3 / a.col3 , não apenas col3 . Isso é essencial .

Ao contrário de uma “coluna virtual” no Oracle, ela não é incluída automaticamente em um SELECT * FROM tbl_a . Você poderia usar uma VIEW para isso.

Por que isso funciona?

A maneira comum de referenciar uma coluna de tabela é com notação de atributo :

  SELECT tbl_a.col1 FROM tbl_a; 

A maneira comum de chamar uma function é com notação funcional :

  SELECT col3 (tbl_a) ; 

Geralmente, é melhor seguir essas formas canônicas , que concordam com o padrão SQL.

Mas no PostgreSQL, notação funcional e notação de atributo são equivalentes. Então, estes funcionam também:

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

Mais sobre isso no manual.
Você provavelmente já viu até agora, onde isso está indo. Parece que você adicionaria uma coluna extra de tabela tbl_a enquanto col3() é, na verdade, uma function que usa a linha atual de tbl_a (ou seu alias) como argumento de tipo de linha e calcula um valor.

 SELECT *, a.col3 FROM tbl_a AS a; 

Se houver uma coluna col3 real, ela terá prioridade e o sistema não procurará uma function desse nome, tendo a linha tbl_a como parâmetro.

A beleza disso: você pode adicionar ou descartar colunas de tbl_a e a última consulta retornará dinamicamente todas as colunas atuais, onde uma visualização retornaria apenas as colunas que existiam no momento da criação (binding inicial vs. binding tardia de * ).
Claro, você tem que largar a function dependente antes de poder largar a mesa agora. E você tem que tomar cuidado para não invalidar a function ao fazer alterações na tabela.

Além de uma exibição, você pode criar uma function para a sum.

 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; 

e depois usá-lo como seu agregador:

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

Observe que o tipo de retorno de sum_other_table () depende do tipo da coluna que você está resumindo.

Aparentemente isso é tratado com pontos de vista, como por comentário de leão. Então, no meu caso, usei o comando:

 CREATE VIEW  AS SELECT *, (SELECT sum() FROM =) AS  FROM  

que essencialmente me dá outra tabela incluindo a coluna desejada.

Existem três respostas até agora, todas funcionam. Qualquer um deles poderia ser uma “melhor solução” dependendo das circunstâncias. Com tabelas pequenas, o desempenho deve ser bem próximo, mas nenhum deles pode se adaptar bem a tabelas com milhões de linhas. A maneira mais rápida de obter os resultados desejados com um grande dataset provavelmente seria (usando a configuração do 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; 

Se a_id é declarada como uma chave primária, e isto é executado sob 9.1 ou posterior, a cláusula GROUP BY pode ser simplificada porque col1 e col2 são funcionalmente dependentes de a_id .

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

A visão poderia ser definida dessa forma e seria dimensionada, mas eu não acho que todos os mesmos caminhos de execução serão considerados para as abordagens usando funções, então o caminho de execução mais rápido pode não ser usado.