将常见查询存储为列？ [英] Store common query as column?

问题描述

使用PostgreSQL，我有一些查询如下所示：

  SELECT< col 1> col 2> 
，（SELECT sum（< col x>）
 FROM< otherTable> 
 WHERE< other table foreignkeyCol> =< this table keycol>）AS< col 3> 
 FROM< tbl> 
  

鉴于子选择在每种情况下都是相同的，有没有办法存储该子 - 选择作为表中的伪列？基本上，我想能够从表A中选择一个列，该列是来自表B中记录相关的特定列的总和。这是可能吗？

解决方案

有没有办法存储该子选择作为伪 - 列中的列？

A VIEW 喜欢被告知是一个完全有效的解决方案。但还有另一种方式更适合您的问题。您可以编写一个将表格类型作为参数的函数，以 计算字段或生成列 strong>。

考虑这个测试用例，从您的描述中导出：

  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的功能 p>

  CREATE FUNCTION col3（tbl_a）
 RETURNS int8 AS 
 $ func $ 
 SELECT sum ）
 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）; 
一般来说， strong>坚持这些规范的方式，它们符合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 添加或删除列，最后一个查询将动态返回所有当前列，其中视图只会返回创建时存在的列（早期绑定与 * 的后期绑定）。
 
当然，您必须先删除依赖函数，然后才能放置表。而在更改表格时，您必须小心不要使功能无效。
 
Using PostgreSQL, I have a number of queries that look like this:
SELECT <col 1>, <col 2>
     , (SELECT sum(<col x>)
        FROM   <otherTable> 
        WHERE  <other table foreignkeyCol>=<this table keycol>) AS <col 3>
FROM   <tbl>
Given that the sub-select will be identical in every case, is there a way to store that sub-select as a pseudo-column in the table? Essentially, I want to be able to select a column from table A that is a sum of a specific column from table B where the records are related. Is this possible?
解决方案

  
Is there a way to store that sub-select as a pseudo-column in the table?
A VIEW like has been advised is a perfectly valid solution. But there is another way that fits your question even more closely. You can write a function that takes the table type as parameter to emulate a "computed field" or "generated column".

Consider this test case, derived from your description:
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);
Create function that emulates 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;
Now you can query:
SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;
Or even:
SELECT *, a.col3 FROM tbl_a a;
Note how I wrote tbl_a.col3 / a.col3, not just col3. This is essential.

Unlike a "virtual column" in Oracle it is not included automatically in a SELECT * FROM tbl_a. You could use a VIEW for that.

Why does this work?

The common way to reference a table column is with attribute notation:
SELECT tbl_a.col1 FROM tbl_a;
The common way to call a function is with functional notation:
SELECT col3(tbl_a);
Generally, it's best to stick to these canonical ways, which agree with the SQL standard.

But in PostgreSQL, functional notation and attribute notation are equivalent. So these work as well:
SELECT col1(tbl_a) FROM tbl_a;
SELECT tbl_a.col3;
More about that in the manual.

You probably see by now, where this is going. This looks like you would add an extra column of table tbl_a while col3() is actually a function that takes the current row of tbl_a (or its alias) as row type argument and computes a value.
SELECT *, a.col3
FROM   tbl_a AS a;
If there is an actual column col3 it takes priority and the system does not look for a function of that name taking the row tbl_a as parameter.

The beauty of it: you can add or drop columns from tbl_a and the last query will dynamically return all current columns, where a view would only return such columns that existed at creation time (early binding vs. late binding of *).

Of course, you have to drop the depending function before you can drop the table now. And you have to take care not to invalidate the function when making changes to the table.

                        
这篇关于将常见查询存储为列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！