Mercurial > gemma
changeset 4604:ca8d18f794f3 geoserver_sql_views
Add database update script to add SQL view definition field
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 03 Sep 2019 18:01:10 +0200 |
| parents | d24e951206ca |
| children | 7650d2431f86 |
| files | schema/updates/1112/01.add_view_def.sql schema/version.sql |
| diffstat | 2 files changed, 19 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1112/01.add_view_def.sql Tue Sep 03 18:01:10 2019 +0200 @@ -0,0 +1,18 @@ +CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean +AS $$ +BEGIN + EXECUTE format('SELECT * FROM (%s) AS test', stmt); + RETURN true; +EXCEPTION + WHEN OTHERS THEN + RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; + RETURN false; +END +$$ + LANGUAGE plpgsql + STRICT; + +ALTER TABLE sys_admin.published_services + ALTER name TYPE varchar, + ADD view_def text CHECK (is_valid_from_item(view_def)), + ADD CHECK (to_regclass(name) IS NOT NULL OR view_def IS NOT NULL);