spatialSQL/sicob_executesql.sql at master · erikfva/spatialSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
SET CLIENT_ENCODING TO 'utf8';
CREATE OR REPLACE FUNCTION public.sicob_executesql(_query text, _opt json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
    result jsonb = '{}'::jsonb;
    row_cnt integer;
    _table_name text;
    _schema text;
    _table_out text;
BEGIN
---------------------------
--PARAMETROS DE ENTRADA
---------------------------
--> _query : Consulta SQL a para ser ejecutada.
--> _opt : Parametros con las siguientes opciones:
	-- table_out : Nombre de la tabla resultante as ser creada a partid de la consulta "_query".
    --				Si no es una tabla temporal y no se incluye el nombre de esquema,
    --				la tabla se crea en el esquema 'public'.
    -- temp : Por defecto es false. Indica si la tabla a ser creada sera temporal
    --		  mientras dura la transaccion.
    -- return_scalar : Por defecto es false. Indica si la consulta devuelve un valor.
    -- create_index: Indica si se va agregar a la tabla el indice sicob_id y el indice
    --				espacial para the_geom.

    RAISE NOTICE 'Ejecutando... %', _query;
    _query := 	replace(
                  replace(
                      replace(_query,'=''','='''''),
                      '''",',
                      '''''",'
                  ),
                  '¨',
                  ''''
    			);
    _table_out := COALESCE(_opt->>'table_out','');
    IF( _table_out = '' AND COALESCE((_opt->>'temp')::boolean,FALSE) = TRUE) THEN
    	_table_out := 'f' || MD5(random()::text);
    END IF;
    SELECT * FROM sicob_split_table_name(_table_out) INTO _schema, _table_name;

    IF _table_out <> '' THEN
    	IF COALESCE((_opt->>'temp')::boolean,FALSE) = TRUE THEN
        	EXECUTE 'DROP TABLE IF EXISTS ' || _table_name || ' CASCADE';
        	EXECUTE 'CREATE TEMPORARY TABLE ' || _table_name || ' ON COMMIT DROP AS ' || _query;
            result := jsonb_build_object('table_out', _table_name);
        ELSE
        	EXECUTE 'DROP TABLE IF EXISTS ' || _table_out || ' CASCADE';
        	EXECUTE 'CREATE UNLOGGED TABLE ' || _table_out || ' AS ' || _query;
            result := jsonb_build_object('table_out', _table_out);
        END IF;
    ELSE
    	IF COALESCE((_opt->>'return_scalar')::boolean,FALSE) = TRUE THEN
        	EXECUTE
            '
            WITH
            _fetchdata AS (' || _query || ' )
            SELECT row_to_json(t) FROM _fetchdata t
            ' INTO result;
        ELSE
    		EXECUTE _query;
        END IF;
    END IF;

    GET DIAGNOSTICS row_cnt = ROW_COUNT;
    result := result || jsonb_build_object('row_cnt', row_cnt);

    IF row_cnt > 0 AND _table_out <> '' AND COALESCE((_opt->>'create_index')::boolean,FALSE) = TRUE THEN


        IF COALESCE((_opt->>'temp')::boolean,FALSE) = TRUE THEN
        	IF sicob_exist_column(_table_name, 'sicob_id') THEN
        		EXECUTE 'DROP INDEX IF EXISTS ' || _table_name || '_sicob_id';
                --EXECUTE 'CREATE INDEX ' || _table_name || '_sicob_id ON ' || _table_name || ' USING btree (sicob_id);';
            	EXECUTE 'ALTER TABLE ' || _table_name || ' DROP COLUMN sicob_id';
                EXECUTE Format('ALTER TABLE %s ADD sicob_id SERIAL NOT NULL UNIQUE', _table_name);
        	END IF;
        ELSE
        	IF sicob_exist_column(_table_out, 'sicob_id') THEN
        		--EXECUTE 'ALTER TABLE ' || _table_out || ' ADD PRIMARY KEY (sicob_id);';
            	EXECUTE 'DROP INDEX IF EXISTS ' || _table_name || '_sicob_id';
            	EXECUTE 'ALTER TABLE ' || _table_out || ' DROP COLUMN sicob_id';
                EXECUTE Format('ALTER TABLE %s ADD sicob_id SERIAL NOT NULL UNIQUE', _table_out);
            END IF;
            EXECUTE 'DROP INDEX IF EXISTS ' || _table_name || '_geomid CASCADE';
            EXECUTE 'CREATE INDEX ' || _table_name || '_geomid
            ON ' || _table_out || '
            USING GIST (the_geom) ';
        END IF;


   END IF;

   IF _table_out <> '' AND COALESCE((_opt->>'add_geoinfo')::boolean,FALSE) = TRUE THEN
		PERFORM sicob_add_geoinfo_column(_table_out);
        IF row_cnt > 0 THEN
        	PERFORM sicob_update_geoinfo_column(_table_out);
        END IF;
   END IF;

   RETURN result;

EXCEPTION WHEN others THEN
    RAISE EXCEPTION 'sicob_executesql: -> % , _query: %, _opt: %', SQLERRM, _query, _opt::text;

END;
$function$