How to write a trigger for a given test database?

Question

PostgreSQL

I will describe the situation so that my question is as clear as possible: there is a program that processes "raw" data and enters them into the postgresql database. Through the same program, the user enters his sql queries and analyzes the data from the database. But here's the problem: to analyze some special cases from the database, you need to create a lot of complex queries - which leads to a significant decrease in the speed of analysis. Hence the question: how to create, if possible, a table in which it will be filled automatically (by means of postgres) based on the data of the cells of other tables.
Schematically, we have the following:

But what I would like to have:

I have a rather superficial knowledge of postgres, so I ask for help with this question: what exactly to read, what methods exist to solve the task, etc.
Thanks in advance to everyone who at least read this question =)
UPD The following needs to be clarified: the SQL queries in the mentioned program are set strictly by the program's own syntax (i.e. it is not pure sql), and this syntax supports only a limited number of functions. Therefore, the task is as follows: through pgadmin, add a certain command that will create a new table that will add data based on the built-in logic. Is it possible?
ADDITION:I read about SQL and databases and I would like to clarify some points for myself, so I ask for help from those who understand writing triggers for postgresql. Please help me write a trigger for the following test database: The point is

to create another table:

Where action2_items_table.user_id = user_table.user_id ,
and action2_items_table.item_names is a text field that lists all unique values ​​of user_actions_table.item_id separated by commas for the user provided that user_actions_table.action2 = true.

Answer 1

Welcome to the world of programming on the DBMS side! ;)
Trigger to help you . Link to PL/pgSQL, it is set by default.
Of course, you will have to tinker with logic. The 3 source tables affect the common dependent.
If you're using 9.3 or higher, you can use Materialized Views , although they may not suit your needs. However, before the release of 9.4, there is a limitation: the view must be updated if the original data has changed. The view is not available during the update.
If I understood the problem correctly, then it is solved by such a request:

SELECT ua.item_id, string_agg(i.item_name,',')
  FROM user_actions_table ua
  JOIN item_table USING (item_id)
 WHERE ua.action2
 GROUP BY ua.item_id;

The simplest solution is to build a materialized view like this:

CREATE MATERIALIZED VIEW action2_items_table AS
SELECT ua.item_id, string_agg(i.item_name,',')
  FROM user_actions_table ua
  JOIN item_table USING (item_id)
 WHERE ua.action2
 GROUP BY ua.item_id
 ORDER BY ua.item_id;

It is better to read about the concept in the docs, in short - a table will be made with the structure and data returned by the request, while the request itself will also be saved. The data will be “frozen”, i.e. any changes to the source tables will not be visible. You can update the data with the command : REFRESH MATERIALIZED VIEW action2_items_table;
In the current version of PostgreSQL (9.3 yet, 9.4 is about to be released), this command will lead to locks: `action2_items_table` will not be available until the “update” is completed. Try it, if the given operation will take no more than 1 second, I think it's acceptable.
In 9.4, you can update in parallel with requests :

REFRESH MATERIALIZED VIEW CONCURRENTLY  action2_items_table;

True, this requires a unique index:

CREATE UNIQUE INDEX u_action2_items_table ON action2_items_table(item_id);

By the way, such an index will not hurt in the current (9.3) version.
Writing this functionality procedurally (PL/pgSQL) is difficult, but possible. Let me know if this solution doesn't work for you.

Answer 2

A view will not work?
www.postgresql.org/docs/9.2/static/sql-createview.html