-
Jan David Mol authoredJan David Mol authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
13_lofar_views.sql 12.04 KiB
\c hdb
-- NOTE: We concatenate domain/family/member here, which means we can't index
-- the resulting column. However, queries also supply the attribute name,
-- which we can index. The scan on the device name is then limited to
-- entries which have the same attribute name across devices.
-- DOUBLE --
CREATE OR REPLACE VIEW lofar_array_double AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devdouble att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_double AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devdouble att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- BOOLEAN --
CREATE OR REPLACE VIEW lofar_array_boolean AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
CASE WHEN array_element.val THEN 1 ELSE 0 END AS value
FROM att_array_devboolean att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_boolean AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
CASE WHEN value_r THEN 1 ELSE 0 END AS value
FROM att_scalar_devboolean att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- UCHAR --
CREATE OR REPLACE VIEW lofar_array_uchar AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devuchar att -- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_uchar AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devuchar att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- SHORT --
CREATE OR REPLACE VIEW lofar_array_short AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devshort att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_short AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devshort att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- USHORT --
CREATE OR REPLACE VIEW lofar_array_ushort AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devushort att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_ushort AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devushort att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- LONG -- CREATE OR REPLACE VIEW lofar_array_long AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devlong att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_long AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devlong att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ULONG --
CREATE OR REPLACE VIEW lofar_array_ulong AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devulong att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_ulong AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devulong att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- LONG64 --
CREATE OR REPLACE VIEW lofar_array_long64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devlong64 att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_long64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device, ac.name AS name,
value_r as value
FROM att_scalar_devlong64 att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ULONG64 --
CREATE OR REPLACE VIEW lofar_array_ulong64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devulong64 att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_ulong64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devulong64 att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- FLOAT --
CREATE OR REPLACE VIEW lofar_array_float AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devfloat att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_float AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devfloat att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- STRING --
CREATE OR REPLACE VIEW lofar_array_string AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devstring att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE -- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_string AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devstring att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- STATE --
CREATE OR REPLACE VIEW lofar_array_state AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devstate att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_state AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devstate att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ENCODED --
CREATE OR REPLACE VIEW lofar_array_encoded AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devencoded att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_encoded AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devencoded att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ENUM --
CREATE OR REPLACE VIEW lofar_array_enum AS
SELECT att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devenum att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_enum AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devenum att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;