Mercurial > gemma

comparison schema/gemma.sql @ 4872:5c12b7cdc58c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make CHECK constraint work in sessions with empty search_path CHECK constraints involving is_valid_from_item() failed when applied in a session with empty search_path, if the checked entries contained any non-schema qualified references to database objects (e.g. PostGIS functions in the public schema). This prevented restoring the current default values in sys_admin.published_services from a dump created using pg_dump.
author Tom Gottfried <tom@intevation.de>
date Fri, 24 Jan 2020 16:48:59 +0100
parents 082027fb2d58
children 8a4c98b80fbd
comparison
equal deleted inserted replaced
4871:4d8982fa49f9 4872:5c12b7cdc58c
25 -- 25 --
26 -- Functions to be used in CHECK constraints 26 -- Functions to be used in CHECK constraints
27 -- 27 --
28 28
29 -- Check if a given string can be used as a FROM item in an SQL statement 29 -- Check if a given string can be used as a FROM item in an SQL statement
30 -- All objects in stmt should be schema qualified if not in the public schema
30 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean 31 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean
31 AS $$ 32 AS $$
32 BEGIN 33 BEGIN
33 EXECUTE format('SELECT * FROM (%s) AS test', stmt); 34 EXECUTE format('SELECT * FROM (%s) AS test', stmt);
34 RETURN true; 35 RETURN true;
36 WHEN OTHERS THEN 37 WHEN OTHERS THEN
37 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; 38 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE;
38 RETURN false; 39 RETURN false;
39 END 40 END
40 $$ 41 $$
42 SET search_path TO 'public'
41 LANGUAGE plpgsql 43 LANGUAGE plpgsql
42 STRICT; 44 STRICT;
43 45
44 46
45 -- 47 --