N
N
nurzhannogerbek2019-01-16 12:47:28
PostgreSQL
nurzhannogerbek, 2019-01-16 12:47:28

How to synchronize table and view in PostgreSQL?

Hello colleagues! Please help me figure it out.
I have a simple table in a PostgreSQL database. The structure of this table is as follows.

| ID | REPORT_TYPE | STATUS | USER_EMAIL     |
|--------------------------------------------|
| 1  | sms         | 0      | [email protected]  |
| 2  | call        | 1      | [email protected] |
| 3  | internet    | 2      | [email protected] |

I'm trying to create a representation (view) identical in structure based on this table. That is, if a new record with status 0 appears in the table, it must be entered into the view. If any record in the table has its status value changed to 0, it appears in the view. In a word, I'm trying to synchronize a table and a view in PostgreSQL. Tell me, how is this implemented?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Melkij, 2019-01-16
@melkij

If you are talking about view, then I do not understand you at all.
In principle, there is not and cannot be a single line in the view, it is impossible to write anything to the view itself (writable view is a rewriting of the request). When you access a view, the request goes into the request from that view's declaration and is merrily parsed/rewritten like any other request.
So what are you trying to sync with? Request with another request? You have data in just one place, nothing to sync here.
If you are still talking about a materialized view, then you need a separate request to refresh the materialized view. In general, matview by design for outdated data and the task of periodic updating through refresh.

D
Dmitry, 2019-01-16
@cosmoskey

Everything is simple)

CREATE VIEW report_view AS
SELECT * FROM report;

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question