49 lines
1.5 KiB
JavaScript
49 lines
1.5 KiB
JavaScript
const { Client } = require('pg');
|
|
|
|
async function fixView() {
|
|
const client = new Client({
|
|
host: '87.106.21.21',
|
|
port: 5432,
|
|
user: 'enwelo_admin',
|
|
password: 'WX1t1cgP1qK09',
|
|
database: 'enwelo'
|
|
});
|
|
|
|
try {
|
|
await client.connect();
|
|
|
|
const createViewSql = `
|
|
CREATE OR REPLACE VIEW geodaten.v_projekt_sicherung AS
|
|
SELECT az.projekt_id,
|
|
p.name AS projekt_name,
|
|
a.id AS alkis_id,
|
|
a."FSK" AS fsk,
|
|
a."GNA" AS nachname,
|
|
a."VNA" AS vorname,
|
|
a.bemerkung AS nof_zusatz,
|
|
COALESCE(s.status, 'Unbekannt'::text) AS aktueller_status,
|
|
s.datum AS status_datum,
|
|
r.farbe_hex,
|
|
a.geom
|
|
FROM geodaten.flaecheneigentuemer_alkis_zuweisung az
|
|
JOIN geodaten.flaecheneigentuemer_alkis a ON az.fsk = a."FSK"
|
|
JOIN geodaten.projekte p ON az.projekt_id = p.id
|
|
LEFT JOIN LATERAL ( SELECT flaecheneigentuemer_status.status,
|
|
flaecheneigentuemer_status.datum
|
|
FROM geodaten.flaecheneigentuemer_status
|
|
WHERE flaecheneigentuemer_status.fsk = az.fsk AND flaecheneigentuemer_status.projekt_id = az.projekt_id
|
|
ORDER BY flaecheneigentuemer_status.datum DESC
|
|
LIMIT 1) s ON true
|
|
LEFT JOIN geodaten.ref_sicherungsstatus r ON s.status = r.status_name;
|
|
`;
|
|
|
|
await client.query(createViewSql);
|
|
console.log("View recreated successfully.");
|
|
} catch (e) {
|
|
console.error(e);
|
|
} finally {
|
|
await client.end();
|
|
}
|
|
}
|
|
fixView();
|