0

PostgreSQL. I have two tables. I add data to the first table time by time using insert for all columns.

Table1:

CREATE TABLE purchases (
id INTEGER,
name VARCHAR,
qty INTEGER,
date TIMESTAMP,
price NUMERIC,
about VARCHAR )

Table2:

CREATE TABLE result (
id INTEGER,
name VARCHAR,
qty INTEGER,
date TIMESTAMP,
profit NUMERIC,
sold NUMERIC )

  1. If the NAME (field) of new row in table1 exists in view than just sum the qty of this new row to row qty with such name in view

  2. if the NAME of new row in table1 doesnt exist in view than create new row with same qty as in Table1 new row

3kShoter
  • 5
  • 3
  • I suspect you have an XY problem. – Mitch Wheat Jul 30 '22 at 08:08
  • What is a XY problem? – 3kShoter Jul 30 '22 at 08:18
  • [X-Y Problem](https://meta.stackexchange.com/questions/66377) –  Jul 30 '22 at 08:23
  • Yeah thanks i deleted the text i thought caused the wrong spot. – 3kShoter Jul 30 '22 at 08:26
  • [Why not upload images of code/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) – Luuk Jul 30 '22 at 08:31
  • The definition of tables is better shown as CREATE TABLE statements using [formatted text](https://meta.stackoverflow.com/a/251362) - not using screen shots –  Jul 30 '22 at 08:31
  • 1
    In this case your `Table2` should not be a table, but a [view](https://www.postgresql.org/docs/13/sql-createview.html) – Luuk Jul 30 '22 at 08:35
  • I finished with my logic now and don't know how to do so, that view procs if either one or another tables changes. And after that how to check which table of them changed before any other statement starts. – 3kShoter Jul 31 '22 at 06:57
  • Sorry, it is unclear what you are doing. The view always represents last changes done in your table, there is no need to check. Directly after a modification of your table the result are shown in the view, nothing need to be done, and 'other statements' can start (Although it's unclear what 'other statements' are....) – Luuk Jul 31 '22 at 08:11
  • 1. I mean how to do so, that my view procs not of 1 but of 2 tables changes (when a change in any of 2 tables) 2. And then i want to find out which table's change caused the view to proc before i update my view data from these tables ('other statements') – 3kShoter Jul 31 '22 at 08:12
  • thxs i used triggers, but: https://stackoverflow.com/questions/73183465/why-does-my-trigger-function-cause-a-null-error – 3kShoter Jul 31 '22 at 12:55

1 Answers1

0

A VIEW will 'automatically' reflect any changes done in Table1:

CREATE VIEW Table2 AS
SELECT 
   name, 
   SUM(qty) as Qty,
   MIN(date) as Date,
   AVG(price) as Price,
   '' as about)
FROM Table1
GROUP BY name

After this, you can view the result using:

SELECT * FROM Table2

NOTE: of course, when creating a view you should try to avoid naming it something like Table2 ...

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • How will i view the 'Table2 VIEW' than in future. Can it be seen in chemas folder as other tables? – 3kShoter Jul 30 '22 at 08:50
  • And what query commands use for this logic? a) If the name of new row in table1 exists in view than just sum the qty of this new row to row qty with such name in view b) if the name of new row in table1 doesnt exist in view than create new row with same qty as in Table1 new row – 3kShoter Jul 30 '22 at 08:55
  • A view can be used like a table, for more info read: [wikipedia](https://en.wikipedia.org/wiki/View_(SQL)), or study [Managing PostgreSQL Views](https://www.postgresqltutorial.com/postgresql-views/managing-postgresql-views/) – Luuk Jul 30 '22 at 08:57
  • thxs really helped me. I'll ask for further question if they occur about this topic if you r agree. – 3kShoter Jul 30 '22 at 09:01
  • u can rate the question if u like or dis it)) – 3kShoter Jul 30 '22 at 09:03
  • My suggestion (on your added question) is: Try it! It's not that complex to try, and see what happens, and you will get some experience while trying. – Luuk Jul 30 '22 at 09:33
  • thxs anyway i got u) – 3kShoter Jul 30 '22 at 11:33
  • I finished with my logic now and don't know how to do so, that view procs if either one or another tables changes. And after that how to check which table of them changed before any other statement starts. – 3kShoter Jul 31 '22 at 04:50
  • thxs i used triggers, but: https://stackoverflow.com/questions/73183465/why-does-my-trigger-function-cause-a-null-error – 3kShoter Jul 31 '22 at 12:55