Store common query as column?

Question

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?

Answer 1

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. Go for it.

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   LANGUAGE sql STABLE AS $func$ SELECT sum(colx) FROM   tbl_b b WHERE  b.a_id = $1.a_id $func$; 

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 Postgres also allows attribute notation. 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.

I still wouldn't use it. It's too surprising to the innocent reader.