PostgreSQL - When creating a view use columns that are being created in SELECT to create another col-CodePudding

I want to create view from few tables lets say i have table1 (t1)

ID | Time_of_start | Duration

table2 (t2)

time_of_login | time_of_logout | Name

and i created a view lets call it wwn

t1.ID | t1.Time_of_start | Time_of_end(t1.Time_of_start   t1.Duration) | Name

CREATE VIEW wwn
AS SELECT ID, 
Time_of_start , 
(Time_of_start    Duration * interval '1 minute') as Time_of_end, 
Duration, 
(select name from table2, wwn where (time_of_login, time_of_logout) overlaps (Time_of_start, Time_of_end))
FROM table1 where ID = 300;

and that doesn't work because there is no wwn when this is executed, the only thing i can think of is creating other temporary view. So i need a name to represent who was logined at the time of event between Time_of_start, Time_of_end

Samples

table1 
300 | 2019-07-25 09:13:40 | 20 
300 | 2019-07-25 09:33:40 | 30
table2
2019-07-25 07:13:40 | 2019-07-25 19:32:37 | Anthon
2019-07-25 20:32:37 | 2019-07-26 10:14:20 | John

Desired Result

wwn
300 | 2019-07-25 09:13:40 | 2019-07-25 09:33:40 | 20 | Anthon
300 | 2019-07-25 09:33:40 | 2019-07-25 10:03:40 | 30 | Anthon

CodePudding user response：

The test case (fiddle):

Working test case

Just use a WITH clause term (or derived table) to handle the duration calculation, then use that in the subsequent expression.

Just the query (no view):

WITH cte1 AS (
        SELECT t1.ID, t1.Time_of_start
             , (t1.Time_of_start   t1.Duration * INTERVAL '1' MINUTE) AS Time_of_end
             , Duration
          FROM table1 AS t1
     )
SELECT cte1.*, name
  FROM cte1
  JOIN table2
    ON (time_of_login, time_of_logout) overlaps (Time_of_start, Time_of_end)
;

From that, just create the view:

CREATE VIEW wwm AS
WITH cte1 AS (
        SELECT t1.ID, t1.Time_of_start
             , (t1.Time_of_start   t1.Duration * INTERVAL '1' MINUTE) AS Time_of_end
             , Duration
          FROM table1 AS t1
     )
SELECT cte1.*, name
  FROM cte1
  JOIN table2
    ON (time_of_login, time_of_logout) overlaps (Time_of_start, Time_of_end)
;