api.stays_view
Description
Stays listing web view
Table Definition
CREATE VIEW stays_view AS (
SELECT s.id,
s.name,
m.name AS moorage,
m.id AS moorage_id,
(s.departed - s.arrived) AS duration,
(ARRAY['Unknown'::text, 'Anchor'::text, 'Mooring Buoy'::text, 'Dock'::text])[s.stay_code] AS stayed_at,
s.stay_code AS stayed_at_id,
s.arrived,
"from".id AS arrived_log_id,
"from"._to_moorage_id AS arrived_from_moorage_id,
"from"._to AS arrived_from_moorage_name,
s.departed,
"to".id AS departed_log_id,
"to"._from_moorage_id AS departed_to_moorage_id,
"to"._from AS departed_to_moorage_name,
s.notes
FROM (((api.stays s
JOIN api.moorages m ON ((s.moorage_id = m.id)))
LEFT JOIN api.logbook "from" ON ((("from"._from_time = s.departed) AND ("from"._to_moorage_id IS NOT NULL))))
LEFT JOIN api.logbook "to" ON (("to"._to_time = s.arrived)))
WHERE ((s.departed IS NOT NULL) AND (s.name IS NOT NULL))
ORDER BY s.arrived DESC
)
Columns
| Name | Type | Default | Nullable |
|---|---|---|---|
| arrived | timestamp with time zone | true | |
| arrived_from_moorage_id | integer | true | |
| arrived_from_moorage_name | text | true | |
| arrived_log_id | integer | true | |
| departed | timestamp with time zone | true | |
| departed_log_id | integer | true | |
| departed_to_moorage_id | integer | true | |
| departed_to_moorage_name | text | true | |
| duration | interval | true | |
| id | integer | true | |
| moorage | text | true | |
| moorage_id | integer | true | |
| name | text | true | |
| notes | text | true | |
| stayed_at | text | true | |
| stayed_at_id | integer | true |
Referenced Tables
| Name | Columns | Comment | Type |
|---|---|---|---|
| api.stays | 16 | The stays table records time spent at moorages. Each stay links a moorage with arrival/departure timestamps. RLS policies filter by vessel_id. | BASE TABLE |
| api.moorages | 15 | The moorages table stores locations where vessels can stay (marinas, anchorages, etc.). Each moorage has geographic coordinates and metadata. | BASE TABLE |
| api.logbook | 44 | The logbook table stores vessel navigation entries with timestamps, locations, and trip metrics. RLS policies filter by vessel_id automatically. These indexes optimize different query patterns while minimizing storage. | BASE TABLE |
Relations
erDiagram
"api.stays_view" {
timestamp_with_time_zone arrived ""
integer arrived_from_moorage_id ""
text arrived_from_moorage_name ""
integer arrived_log_id ""
timestamp_with_time_zone departed ""
integer departed_log_id ""
integer departed_to_moorage_id ""
text departed_to_moorage_name ""
interval duration ""
integer id ""
text moorage ""
integer moorage_id ""
text name ""
text notes ""
text stayed_at ""
integer stayed_at_id ""
}
Generated by tbls