Store common query as column?

by

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.

More Related Contents:

  1. Calculating and saving space in PostgreSQL
  2. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  3. Making sense of Postgres row sizes
  4. Do nullable columns occupy additional space in PostgreSQL?
  5. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  6. CONSTRAINT to check values from a remotely related table (via join etc.)
  7. Grant all on a specific schema in the db to a group role in PostgreSQL
  8. Does a Postgres UNIQUE constraint imply an index?
  9. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  10. How to see the CREATE VIEW code for a view in PostgreSQL?
  11. PostgreSQL – set a default cell value according to another cell value
  12. Insert, on duplicate update in PostgreSQL?
  13. “psql: could not connect to server: Connection refused” Error when connecting to remote database
  14. postgresql duplicate key violates unique constraint
  15. ClassNotFoundException with PostgreSQL and JDBC
  16. How to get function parameter lists (so I can drop a function)
  17. How to convert a postgres database to sqlite
  18. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  19. Error when setting n_distinct using a plpgsql variable
  20. Docker wait for postgresql to be running
  21. Function to loop through and select data from multiple tables
  22. Switch role after connecting to database
  23. SQL Sub queries in check constraint
  24. When do Postgres column or table names need quotes and when don’t they?
  25. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  26. PostgreSQL how to concat interval value ‘2 days’
  27. Displaying image in Ireports using PostgreSql
  28. Temporal database design, with a twist (live vs draft rows)
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment