Should I Store Common Subqueries as Table Columns in PostgreSQL?

Store frequently used queries as table columns?

Query:

Consider the following PostgreSQL query:

<code class="language-sql">SELECT <col>, <col>
     , (SELECT sum(<colx>)
        FROM   <othertable> 
        WHERE  <otherforeignkeycol> = <thiskeycol>) AS <col>
FROM   <tbl>;</code>

This subquery remains the same across multiple queries. Is it possible to store this subquery as a pseudo column of a table so that I can easily select a column in table A that represents the sum of the columns in table B based on a record relationship?

Answer:

1. View

Creating a view is an efficient solution that allows you to define a virtual table with the required subquery as one of its columns.

2. Calculated fields (storage generated columns)

The storage-generated columns introduced in PostgreSQL 11 meet your requirements. These allow you to define a column that is dynamically calculated based on the values ​​of other columns (including subqueries).

Implementation using calculated fields:

Consider the following form:

<code class="language-sql">CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
CREATE TABLE tbl_b (b_id int, a_id int, colx int);</code>

Create a calculated field using a function:

<code class="language-sql">CREATE FUNCTION col3(a_id INT)
  RETURNS int8
  LANGUAGE sql STABLE AS
$func$
SELECT sum(colx)
FROM   tbl_b b
WHERE  b.a_id = 
$func$;</code>

Now you can query:

<code class="language-sql">SELECT a_id, col1, col2, col3(a_id)
FROM   tbl_a;</code>

Instructions:

Calculated fields simulate a subquery by providing the value of a specified column based on the current row in the table. Access calculated fields using attribute notation (for example, col3(a_id)). This technique allows dynamic retrieval of calculated values ​​and maintains compatibility with SELECT * queries. Note that the function definition has been modified here, using a_id directly as the parameter, avoiding the use of record writing and simplifying the query.

The above is the detailed content of Should I Store Common Subqueries as Table Columns in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!