api.moorages_stays_view
Description
Moorages stay listing web view
Table Definition
CREATE VIEW moorages_stays_view AS (
SELECT _to.name AS _to_name,
_to.id AS _to_id,
_to._to_time,
_from.id AS _from_id,
_from.name AS _from_name,
_from._from_time,
s.stay_code,
s.duration,
m.id,
m.name
FROM (((api.stays s
JOIN api.moorages m ON ((m.id = s.moorage_id)))
LEFT JOIN api.logbook _from ON ((_from._from_time = s.departed)))
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 _to._to_time DESC
)
Columns
| Name | Type | Default | Nullable |
|---|---|---|---|
| _from_id | integer | true | |
| _from_name | text | true | |
| _from_time | timestamp with time zone | true | |
| _to_id | integer | true | |
| _to_name | text | true | |
| _to_time | timestamp with time zone | true | |
| duration | interval | true | |
| id | integer | true | |
| name | text | true | |
| stay_code | 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.moorages_stays_view" {
integer _from_id ""
text _from_name ""
timestamp_with_time_zone _from_time ""
integer _to_id ""
text _to_name ""
timestamp_with_time_zone _to_time ""
interval duration ""
integer id ""
text name ""
integer stay_code ""
}
Generated by tbls