spatialSQL/sicob_execute.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
SET CLIENT_ENCODING TO 'utf8';
CREATE OR REPLACE FUNCTION public.sicob_execute(_query text, _opt json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
    _key text;
    _table_to_chunk text;
    _filter text;
    _txt_chunk_condition text;
    _txt_chunk_id text;
    _partitions int;
    _workers int;

    listSQL text[];

    sql text;
    db text;
    username text;
    connectstring text;
    conn text;
    r record;
    condition text;
    dispatch_result integer;
    dispatch_error text;
    n integer;
    num_done integer;
    status integer;
    processes jsonb[];
    result jsonb;
    results jsonb = '[]';
BEGIN

	--_query := 'SELECT sicob_obtener_predio(''{"lyr_in":"processed.f20170718fagebdcf580ac83_nsi","condition":"<chunk_condition>","subfix":"_p<chunk_id>","tolerance":"5.3"}'')';
   -- _opt := '{"table_to_chunk":"processed.f20170718fagebdcf580ac83_nsi", "partitions":"20","workers":10}'::json;
	_workers := COALESCE( (_opt->>'workers')::int, 1);
SELECT sicob_paralellsql(
  sicob_splitSQL(_query::text,_opt::json)
,('{"max_connections":' || _workers || '}')::json) INTO results;
   RETURN results;

EXCEPTION WHEN others THEN
    RAISE EXCEPTION 'sicob_execute: _query -> %   %', SQLERRM, SQLSTATE;

END;
$function$