Mercurial > gemma
changeset 4173:d3fb2f37380b
Schema qualify tables in function body
This prevents errors or unexpected results in situations where the
search_path does otherwise not allow to find the expected table.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 05 Aug 2019 16:45:48 +0200 |
| parents | 5a650cde0574 |
| children | 00279b36cffd |
| files | schema/gemma.sql schema/isrs.sql schema/updates/1106/01.schemaqualify_tables.sql |
| diffstat | 3 files changed, 14 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Mon Aug 05 16:43:31 2019 +0200 +++ b/schema/gemma.sql Mon Aug 05 16:45:48 2019 +0200 @@ -228,7 +228,7 @@ CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int LANGUAGE sql AS $$ - SELECT max(version) FROM gemma_schema_version; + SELECT max(version) FROM public.gemma_schema_version; $$;
--- a/schema/isrs.sql Mon Aug 05 16:43:31 2019 +0200 +++ b/schema/isrs.sql Mon Aug 05 16:45:48 2019 +0200 @@ -23,7 +23,7 @@ CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean AS $$ - SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code) + SELECT EXISTS(SELECT 1 FROM public.countries WHERE country_code = code) $$ LANGUAGE sql STABLE PARALLEL SAFE;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1106/01.schemaqualify_tables.sql Mon Aug 05 16:45:48 2019 +0200 @@ -0,0 +1,12 @@ +CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int + LANGUAGE sql + AS $$ + SELECT max(version) FROM public.gemma_schema_version; +$$; + +CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean +AS $$ + SELECT EXISTS(SELECT 1 FROM public.countries WHERE country_code = code) +$$ + LANGUAGE sql + STABLE PARALLEL SAFE;