-
Notifications
You must be signed in to change notification settings - Fork 0
Utilities and etc
Peter edited this page Nov 24, 2021
·
3 revisions
Lembrete, depois de write:
sudo chown peter:peter /tmp/test.txt; more /tmp/test.txt- lib.brdate2isodate(text,text default NULL) → text
- lib.check_is(text,JSONb) → boolean
- lib.initcap_sep(text,text DEFAULT '\s.:;,') → text
- lib.isodate2brdate(text) → text
- lib.junpack(
- lib.ltree_pretty(
- lib.multimetaphone(
- lib.multimetaphone(
- lib.name2lex(
- lib.normalizeterm(
- lib.normalizeterm2(
- lib.preserve_notnull(
- lib.set_kx(
- lib.set_obj(
- lib.supertrim(
- lib.to_jsonb(
- lib.to_jsonb(
- lib.to_texts( JSON ) → text[]
- lib.to_texts( JSONb ) → text[]
- lib.txt_to_pairs(
- lib.urn_ltquery(text) → ltxtquery
- lib.urn_nlevel(text,text DEFAULT ':') → int
- lib.urn_prefix(
- lib.urn_prefix_array(
- lib.urn_prefix_get( -- em uso?
- lib.urn_prefix_labels(
- lib.urn_pretty(
- lib.urn_pretty_partes(
- lib.urn_rm_schema(text) → text
- lib.urn_to_ltree(
- lib.xpath_byid(
- lib.xpath_byparent(
- lib.xpath_byparent_1txt(xml,text,text DEFAULT '') → text AS
$wrap$
- ROUND(float,int) → NUMERIC
- ST_GeomFromGeoJSON_sanitized( p_j JSONb, p_srid int DEFAULT 4326) → geometry
- bbox_bounds(p_geom geometry) → jsonb[]
- bigint_base_encode(
- bigint_base_encode(
- bigint_base_encode(
- code_cut_prefix(
- code_format(
- fn_array_agg_notnull (
- get_utmzone(
- get_utmzone_bydump(p_geom geometry) → integer[]
- nameprop_firstlast_str(text) → text AS
$wrap$ - read_geojson(
- srid_name(p_srid int[], p_cut_pref boolean DEFAULT false) → text[]
- ST_AsGeoJSONb( -- ST_AsGeoJSON_complete
- array_distinct_sort (
- array_intersect(anyarray, anyarray) → anyarray
- array_merge_sort(
- array_subtraction(anyarray, anyarray) → anyarray
- bag.cat(jsonb,jsonb) → JSONb
- bigint_base_decode(
- bigint_base_decode(
- bigint_geohash_b32tob4(
- bigint_geohash_b4tob32(
- bigint_geohash_decode(
- bigint_geohash_encode(
- copy_csv(
- file_debug_glob(p_scan text) → text
- file_get_contents(p_file text) → text AS $$
- file_put_contents(
- file_rename(p_file1 text, p_f2 text) → void
- file_rename_glob(
- iIF(
- jsonb_array_totext(JSONb) → text[] AS $$
- jsonb_build_object_notnull(p_j JSONb,) → JSONb
- jsonb_merge_sum( jsonb, jsonb ) → jsonb
- jsonb_object_1key( p_j jsonb ) → text
- jsonb_select_keys(jsonb, text[]) → jsonb
- jsonb_toempty(p_j1 JSONb, p_must_object boolean true) → JSONb
- lib.array_pop_off(ANYARRAY) → ANYARRAY
- lib.concat_notnull(
- lib.json_array_castext(json) → text[]
- lib.url_tocmp(url text) → text
- nameprop_firstlast(
- sha(bytea,text DEFAULT 'sha1') → text AS $$
- sha(text,text DEFAULT 'sha1') → text AS $$
- std_setmaxval(
- unicode_normalize(str text) → text
- xml_to_string(
-- CREATE EXTENSION IF NOT EXISTS intarray; -- mais rápido! segundo https://stackoverflow.com/a/12870458/287948
-- confira https://www.postgresql.org/docs/current/static/intarray.html
-- FALTA INCLUIR HISTORGRAMAS:
-- https://tapoueh.org/blog/2014/02/postgresql-aggregates-and-histograms/
-- https://wiki.postgresql.org/wiki/Aggregate_Histogram
-- http://mathworld.wolfram.com/Class.html
--- Python LIB = PostgreSQL workarounds, to avoid "bugs"
CREATE or replace FUNCTION unicode_normalize(str text) RETURNS text as $f$
# check details at https://stackoverflow.com/questions/24863716
import unicodedata
return unicodedata.normalize('NFC', str.decode('UTF-8'))
$f$ LANGUAGE PLPYTHONU STRICT;
CREATE or replace FUNCTION iIF(
--https://stackoverflow.com/a/53750984/287948
condition boolean, -- IF condition
true_result anyelement, -- THEN
false_result anyelement -- ELSE
) RETURNS anyelement AS $f$
SELECT CASE WHEN condition THEN true_result ELSE false_result END
$f$ LANGUAGE SQL IMMUTABLE;
CREATE or replace FUNCTION nameprop_firstlast(
p_nome text,
p_not text[] DEFAULT array['júnior','junior','jr','jr.','filho','filha','neto','neta', 'i', 'ii', 'iii', 'v', 'iv', 'x']
-- caution need 4-juniors, and can't use romans (chinese 'Xi' is not 11), only the most frequents.
-- must be REGEX (opt to detect sufix separator instead dander sufixes-list)
) RETURNS text[] AS $f$
SELECT CASE
WHEN p_not IS NOT NULL AND lower(x[lst])=ANY(p_not) THEN array[
x[lst-1] || iIF( lower(x[lst])=ANY(p_not[1:4]), ' Júnior', ' '||x[lst] )
,array_to_string(x[1:lst-2],' ')
] ELSE array[ x[lst] , array_to_string(x[1:lst-1],' ') ]
END
FROM (SELECT x, array_upper(x,1) lst FROM (SELECT regexp_split_to_array(trim($1), '\s+')) t1(x)) t2(x)
$f$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION nameprop_firstlast_str(text) RETURNS text AS $wrap$
SELEcT x[1]||', '||x[2]
FROM (SELECT nameprop_firstlast($1)) t(x)
$wrap$ LANGUAGE sql IMMUTABLE STRICT;
/**
* COPY TO CSV HEADER.
*/
CREATE or replace FUNCTION copy_csv(
p_filename text,
p_query text,
p_useheader boolean = true,
p_root text = '/tmp/'
) RETURNS text AS $f$
BEGIN
EXECUTE format(
'COPY (%s) TO %L CSV %s'
,p_query
,p_root||p_filename
,CASE WHEN p_useheader THEN 'HEADER' ELSE '' END
);
RETURN p_filename;
END;
$f$ LANGUAGE plpgsql STRICT;
CREATE or replace FUNCTION sha(bytea,text DEFAULT 'sha1') RETURNS text AS $$
SELECT encode(digest($1,$2), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
CREATE or replace FUNCTION sha(text,text DEFAULT 'sha1') RETURNS text AS $$
-- testou?
SELECT encode(digest($1,$2), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
----
/**
* Convert XML contents into text, as DOM's nodeValue.
* Warning: spaces not exactly as nodeValue, in the case of HTML lists (OL tags) eg.
* Typical use: to_tsvector('portuguese'::regconfig, xml_to_string(content)) as kx_tsvector
*/
CREATE or replace FUNCTION xml_to_string(
xml,
boolean DEFAULT false -- ignore label tag
) RETURNS text AS $f$
SELECT array_to_string(
xpath(CASE WHEN $2 THEN '//*[not(name()="label")]/text()' ELSE '//text()' END, $1)::text[]
, ''
)
$f$ language SQL IMMUTABLE;
----
/*
CREATE or replace FUNCTION file_rename(p_file1 text, p_f2 text) RETURNS void AS $f$
# v0.1
# need sudo chown postgres:postgres path/
import os
os.rename(args[0],args[1])
$f$ LANGUAGE PLpythonU;
CREATE or replace FUNCTION file_rename_glob(
p_scan text, p_path text
) RETURNS void AS $f$
# v0.1
# need sudo chown postgres:postgres p_path/
import os
import glob
for file in list( glob.glob(args[0]) ):
fpath, fname = os.path.split(file)
os.rename( file, args[1]+"/"+fname )
$f$ LANGUAGE PLpythonU;
CREATE or replace FUNCTION file_debug_glob(p_scan text) RETURNS text AS $f$
# v0.1
# need sudo chown postgres:postgres p_path/
import os
import glob
x=''
for file in list( glob.glob(args[0]) ):
fpath, fname = os.path.split(file)
x=x+"\n\t"+ file+"=/tmp/test/"+fname
return "ok:\n"+x
$f$ LANGUAGE PLpythonU;
-- problem error 1, sudo pip install --user packagename
-- import shutil
-- shutil.move('oldname', 'renamedfiles/newname')
*/
CREATE or replace FUNCTION file_get_contents(p_file text) RETURNS text AS $$
import os.path
if not os.path.isfile(args[0]):
return None
with open(args[0],"r") as content_file:
content = content_file.read()
return content
$$ LANGUAGE PLpythonU STRICT;
CREATE or replace FUNCTION file_put_contents(
p_file text,
p_content text,
p_msg text DEFAULT ' (file "%s" saved!) '
) RETURNS text AS $$
# see https://stackoverflow.com/a/48485531/287948
import os.path
dn = os.path.dirname(args[0])
if not dn:
return ('ERROR, directory "%s" not exists or is invalid' % dn)
o=open(args[0],"w")
o.write(args[1]) # no +"\n", no magic EOL
o.close()
if args[2] and args[2].find('%s')>0 :
return (args[2] % args[0])
else:
return args[2]
$$ LANGUAGE PLpythonU STRICT;
-----
/**
* Automatic setval() for standard restart.
* See https://stackoverflow.com/a/49832780/287948
*/
CREATE or replace FUNCTION std_setmaxval(
p_tname text,
p_id_name text DEFAULT 'id'
) RETURNS SETOF bigint AS $f$
BEGIN
RETURN QUERY EXECUTE format(
'SELECT setval(pg_get_serial_sequence(%L,%L), COALESCE((SELECT MAX(%s)+1 FROM %s), 1) , false)'
,p_tname, p_id_name, p_id_name, p_tname
);
END
$f$ LANGUAGE PLpgSQL STRICT;
----
CREATE or replace FUNCTION jsonb_array_totext(JSONb) RETURNS text[] AS $$
-- workaround to convert json array into SQL text[]
-- ideal is PostgreSQL to (internally) convert json array into SQL-array of JSONb values
SELECT array_agg(x) FROM jsonb_array_elements_text($1) t(x);
$$ language SQL IMMUTABLE STRICT;
-- jsonb_*() functions extends https://www.postgresql.org/docs/current/static/functions-json.html
-- st_*() functions extends PostGIS functions
-- array_*() functions extends https://www.postgresql.org/docs/current/static/functions-array.html
-- Arquivo temporário para posterior registribuição
-- puxar todos Snippets como https://wiki.postgresql.org/wiki/Aggregate_Array
-- JSONb snippets, parte1:
CREATE or replace FUNCTION jsonb_object_1key( p_j jsonb ) RETURNS text AS $f$
SELECT x FROM jsonb_object_keys($1) t(x) LIMIT 1
$f$ LANGUAGE SQL IMMUTABLE;
--- POSTGIS Snippets:
CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $$
SELECT ROUND($1::numeric,$2);
$$ language SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION bbox_bounds(p_geom geometry) RETURNS jsonb[] AS $f$
SELECT jsonb_build_object('minlat',u[2], 'minlon',u[1], 'maxlat',u[4], 'maxlon',u[5])
FROM (
SELECT array_agg_cat( array[st_x(geom),st_y(geom)] ) u
FROM ST_DumpPoints( ST_Envelope(p_geom) )
) t
$f$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION ST_GeomFromGeoJSON_sanitized( p_j JSONb, p_srid int DEFAULT 4326) RETURNS geometry AS $f$
-- do ST_GeomFromGeoJSON() with correct SRID. OLD geojson_sanitize().
-- as https://gis.stackexchange.com/a/60945/7505
SELECT g FROM (
SELECT ST_GeomFromGeoJSON(g::text)
FROM (
SELECT CASE
WHEN p_j IS NULL OR p_j='{}'::JSONb OR jsonb_typeof(p_j)!='object'
OR NOT(p_j?'type')
OR (NOT(p_j?'crs') AND (p_srid<1 OR p_srid>998999) )
OR p_j->>'type' NOT IN ('Feature', 'FeatureCollection', 'Position', 'Point', 'MultiPoint',
'LineString', 'MultiLineString', 'Polygon', 'MultiPolygon', 'GeometryCollection')
THEN NULL
WHEN NOT(p_j?'crs') OR 'EPSG0'=p_j->'crs'->'properties'->>'name'
THEN p_j || ('{"crs":{"type":"name","properties":{"name":"EPSG:'|| p_srid::text ||'"}}}')::jsonb
ELSE p_j
END
) t2(g)
WHERE g IS NOT NULL
) t(g)
WHERE ST_IsValid(g)
$f$ LANGUAGE SQL IMMUTABLE;
CREATE or replace FUNCTION jsonb_toempty(p_j1 JSONb, p_must_object boolean true) RETURNS JSONb AS $f$
CASE
WHEN p_j IS NULL OR (p_must_object AND jsonb_typeof(p_j)!='object') THEN '{}'::jsonb
ELSE p_j
END
$f$ LANGUAGE SQL IMMUTABLE;
CREATE or replace FUNCTION jsonb_build_object_notnull(p_j JSONb,) RETURNS JSONb AS $f$
CASE
WHEN p_j IS NULL OR (p_must_object AND jsonb_typeof(p_j)!='object') THEN '{}'::jsonb
ELSE p_j
END
$f$ LANGUAGE SQL IMMUTABLE;
CREATE or replace FUNCTION ST_AsGeoJSONb( -- ST_AsGeoJSON_complete
p_geom geometry, p_decimals int default 6, p_options int default 3,
p_id text default null, p_properties jsonb default null,
p_name text default null, p_title text default null,
p_id_as_int boolean default false
) RETURNS JSONb AS $f$
-- Do ST_AsGeoJSON() adding id, crs, properties, name and title
SELECT ST_AsGeoJSON(p_geom,p_decimals,p_options)::jsonb
|| CASE
WHEN p_properties IS NULL OR jsonb_typeof(p_properties)!='object' THEN '{}'::jsonb
ELSE jsonb_build_object('properties',p_properties)
END
|| CASE
WHEN p_id IS NULL THEN '{}'::jsonb WHEN p_id_as_int THEN jsonb_build_object('id',p_id::bigint)
ELSE jsonb_build_object('id',p_id)
END
|| CASE WHEN p_name IS NULL THEN '{}'::jsonb ELSE jsonb_build_object('name',p_name) END
|| CASE WHEN p_title IS NULL THEN '{}'::jsonb ELSE jsonb_build_object('title',p_title) END
$f$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION code_format(
p_code text,
p_sep text DEFAULT '.'
) RETURNS text AS $f$
SELECT CASE WHEN length(p_code)>3 THEN substr(p_code,1,3) || p_sep || substr(p_code,4) ELSE p_code END
$f$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION code_cut_prefix(
p_prefix text,
p_code text,
p_maxlen int DEFAULT 0
) RETURNS text AS $f$
SELECT regexp_replace(CASE WHEN p_maxlen>0 THEN substr(p_code,1,p_maxlen) ELSE p_code END, '^'||p_prefix, '') ;
$f$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION read_geojson(
p_path text,
p_ext text DEFAULT '.geojson',
p_basepath text DEFAULT '/opt/gits/city-codes/data/dump_osm/'::text,
p_srid int DEFAULT 4326
) RETURNS geometry AS $f$
SELECT CASE WHEN length(s)<30 THEN NULL ELSE geojson_sanitize(s::jsonb) END
FROM ( SELECT readfile(p_basepath||p_path||p_ext) ) t(s)
$f$ LANGUAGE SQL IMMUTABLE;
-----
CREATE OR REPLACE FUNCTION get_utmzone(
p_geom geometry(POINT),
p_sirgas boolean DEFAULT false -- for Brazil only
) RETURNS integer AS $f$
SELECT CASE
WHEN p_geom IS NULL OR GeometryType(p_geom) != 'POINT' THEN NULL
ELSE floor((ST_X(p_geom)+180.0)/6.0)::int
+ CASE WHEN ST_Y(p_geom)>0.0 THEN 32601 ELSE 32701 END
- CASE WHEN p_sirgas THEN 740 ELSE 0 END
END
$f$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION get_utmzone_bydump(p_geom geometry) RETURNS integer[] AS $f$
SELECT array_agg( DISTINCT get_utmzone(geom) )
FROM ST_DumpPoints( p_geom )
$f$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION srid_name(p_srid int[], p_cut_pref boolean DEFAULT false) RETURNS text[] AS $f$
SELECT array_agg( CASE
WHEN p_cut_pref THEN regexp_replace(x,'^.+/[^0-9]+','')
ELSE x
END
)
FROM (
SELECT (regexp_matches(srtext,'PROJCS\["([^"]+)'))[1]
FROM spatial_ref_sys WHERE srid=ANY($1)
) t(x)
$f$ LANGUAGE SQL IMMUTABLE;
-- ex. select srid_name( utm_zone_srid, true ) from test_city
---
CREATE FUNCTION lib.multimetaphone(
-- to use with tsvector_text() without labels
-- Converts string (spaced words) into standard sequence of metaphones.
-- Copied from tlib.normalizeterm(). Check optimization with
--
p_in text[], -- 1. input string (many words separed by spaces or punctuation)
p_len int DEFAULT 6, -- 2. metaphone (max) length
p_min int DEFAULT 1, -- 3. minimal length
p_sep text DEFAULT ' ' -- 4. separator
) RETURNS text AS $f$
SELECT trim( string_agg(CASE WHEN w='' THEN NULL ELSE w END,p_sep) ,p_sep)
FROM (
SELECT metaphone(w,p_len)
FROM unnest($1) AS t1(w)
) t2(w)
WHERE char_length(w)>=p_min
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION lib.multimetaphone(
p_in text, -- 1. input string (many words separed by spaces or punctuation)
p_len int DEFAULT 6, -- 2. metaphone (max) length
p_min int DEFAULT 1, -- 3. minimal length
p_sep text DEFAULT ' ', -- 4. separator
p_rgx text DEFAULT '[^\w]+' -- 5. cut-string regular expression
) RETURNS text AS $wrap$
SELECT lib.multimetaphone( regexp_split_to_array(p_in,p_rgx), $2, $3, $4);
$wrap$ LANGUAGE SQL IMMUTABLE;
------------------
------ String utils.
CREATE FUNCTION lib.normalizeterm(
--
-- Converts string into standard sequence of lower-case words.
-- Para uso nas URNs, NAO USAR para normalização de texto em geral TXT.
--
text, -- 1. input string (many words separed by spaces or punctuation)
text DEFAULT ' ', -- 2. output separator
int DEFAULT 0, -- 3. max lenght of the result (system limit). 0=full.
p_sep2 text DEFAULT ' , ' -- 4. output separator between terms
) RETURNS text AS $f$
SELECT substring(
LOWER(TRIM( regexp_replace( -- for review: regex(regex()) for ` , , ` remove
trim(regexp_replace($1,E'[\\n\\r \\+/,;:\\(\\)\\{\\}\\[\\]="\\s ]*[\\+/,;:\\(\\)\\{\\}\\[\\]="]+[\\+/,;:\\(\\)\\{\\}\\[\\]="\\s ]*|[\\s ]+[–\\-][\\s ]+',
p_sep2, 'g'),' ,'), -- s*ps*|s-s
E'[\\s ;\\|"]+[\\.\'][\\s ;\\|"]+|[\\s ;\\|"]+', -- s.s|s
$2,
'g'
), $2 )),
1,
CASE WHEN $3<=0 OR $3 IS NULL THEN char_length($1) ELSE $3 END
);
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION lib.supertrim(
-- sanitize text
--
text, -- 1. input string (many words separed by spaces or punctuation)
text DEFAULT ' ' -- 2. output separator
) RETURNS text AS $f$
SELECT
TRIM( regexp_replace( -- for review: regex(regex()) for ` , , ` remove
TRIM(regexp_replace($1,E'[\\n\\r \\+/,;:\\(\\)\\{\\}\\[\\]="\\s ]*[\\+/,;:\\(\\)\\{\\}\\[\\]="]+[\\+/,;:\\(\\)\\{\\}\\[\\]="\\s ]*|[\\s ]+[–\\-][\\s ]+',
$2, 'g'),' ,'), -- s*ps*|s-s
E'[\\s ;\\|"]+[\\.\'][\\s ;\\|"]+|[\\s ;\\|"]+', -- s.s|s
$2,
'g'
))
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION lib.normalizeterm2(
text,
boolean DEFAULT true -- cut
) RETURNS text AS $f$
SELECT ( lib.normalizeterm(
CASE WHEN $2 THEN substring($1 from '^[^\(\)\/;]+' ) ELSE $1 END,
' ',
255,
' / '
));
$f$ LANGUAGE SQL IMMUTABLE;
-------- URN LEX Functions
/**
* The path-lenght of the URN lex. For instance urn_nlevel('a:b:c')=3.
* WARNING: not ignores URN-type declaration, so urn_nlevel('urn:lex:a:b:c')=5.
* Name function from Java, StringUtils.countMatches(s,c). Spring countOccurrencesOf()
*/
CREATE FUNCTION lib.urn_nlevel(text,text DEFAULT ':') RETURNS int AS $f$
SELECT CHAR_LENGTH($1) - CHAR_LENGTH(REPLACE($1,$2,'')) + 1;
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.urn_rm_schema(text) RETURNS text AS $f$
SELECT regexp_replace($1,'^urn:[a-z]+:','');
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.ltree_pretty(
ltree,
text DEFAULT '▻', -- opts '▹', '»', '⨠', '>' (lembrar tb campo:valor ⨠ campo:valor da URN)
text DEFAULT chr(8239) -- to add NARROW NO-BREAK SPACE
) RETURNS text AS $f$
SELECT replace(
$1::text,
'.',
CASE WHEN $3>'' THEN $3||$2||$3 ELSE $2 END
)
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.urn_pretty(
p_urn text
,p_split text DEFAULT ':'
,p_sp text DEFAULT ' ' -- chr(8239) = NARROW NO-BREAK SPACE
,p_sep text DEFAULT '▹' -- '▹', '»', '⨠', '>' (lembrar tb campo:valor ⨠ campo:valor da URN)
) RETURNS text AS $f$
SELECT replace(
$1,
p_split,
CASE WHEN $3>'' THEN $3||$2||$3 ELSE $2 END
)
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.urn_pretty_partes(
p_urn text
,p_split text DEFAULT ':'
,p_sp text DEFAULT ' ' -- chr(8239) = NARROW NO-BREAK SPACE
,p_sep text DEFAULT '▹' -- '▹', '»', '⨠', '>' (lembrar tb campo:valor ⨠ campo:valor da URN)
,p_labels text[] DEFAULT array['Jurisdição','Autoridade','TipoDocumento','Descritor']
) RETURNS text AS $f$
--SELECT lib.urn_pretty($1,p_split,p_sp,p_sep)
SELECT array_to_string(
array_agg(p_labels[i]||': '||x),
CASE WHEN p_sp>'' THEN p_sp||p_sep||p_sp ELSE p_sep END
)
FROM (
SELECT row_number() over () as i, x
FROM regexp_split_to_table( $1, p_split ) t(x)
) t2
$f$ language SQL IMMUTABLE;
/**
* Extracts the URN prefix of an URN LEX. Use p_len=NULL or p_len=-1 for no-reduction.
* p_len is the lenght of the URN LEX prefix, as lib.urn_nlevel()
* 1=jurisdiction; 2=ajur-autororithy; 3-jur-aut-typeDoc; 4-full
* @param 1 input URN LEX
* @param p_len
* @param p_rmfrist not tull to remove the first when match.
* @param p_last flag to true to return only the last item.
*/
CREATE FUNCTION lib.urn_prefix_array(
text,
p_len int DEFAULT 3,
p_rmfrist text DEFAULT NULL, -- LIXO, nao deveria existir!
p_last boolean DEFAULT false
) RETURNS text[] AS $f$
SELECT CASE
WHEN p_len IS NULL OR p_len<0 THEN aa
WHEN p_len<t2.first THEN array[]::text[]
ELSE CASE WHEN p_last THEN aa[p_len:p_len] ELSE aa[t2.first:p_len] END
END
FROM (
SELECT regexp_split_to_array($1, ':')
) t(aa), LATERAL (
SELECT CASE WHEN p_rmfrist IS NOT NULL AND t.aa[1]=p_rmfrist THEN 2 ELSE 1 END
) t2(first)
$f$ language SQL IMMUTABLE;
COMMENT ON FUNCTION lib.urn_prefix_array(text,int,text,boolean)
IS 'Extracts the URN prefix of an URN LEX and return exploded into array.';
CREATE FUNCTION lib.urn_prefix_labels(
text,
p_len int DEFAULT -1,
p_labels text[] DEFAULT array['jur','aut','tdoc','descr_full','etc']
) RETURNS JSONb AS $f$
SELECT jsonb_object(p_labels[1:array_length(a,1)], a)
FROM (SELECT lib.urn_prefix_array($1,p_len) ) t(a)
$f$ language SQL IMMUTABLE;
COMMENT ON FUNCTION lib.urn_prefix_labels(text,int,text[])
IS 'Extracts the URN prefix of an URN LEX and return exploded into json.';
CREATE FUNCTION lib.isodate2brdate(text) RETURNS text AS $f$
SELECT CASE
WHEN array_length(x,1)>1 THEN concat(x[3],'/',x[2],'/',x[1])
ELSE x[1]
END
FROM (SELECT regexp_split_to_array($1, '-')) t(x)
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.brdate2isodate(text,text default NULL) RETURNS text AS $f$
SELECT CASE
WHEN array_length(x,1)>1 THEN concat(x[3],'-',x[2],'-',x[1])
ELSE CASE WHEN $2 IS NOT NULL THEN $2||$1 ELSE NULL END
END
FROM (SELECT regexp_split_to_array(COALESCE($1,''), '/')) t(x)
$f$ language SQL IMMUTABLE;
----
CREATE FUNCTION lib.urn_prefix_get( -- em uso?
text, p_len int DEFAULT 3, p_rmfrist text DEFAULT NULL
) RETURNS text AS $wrap$
SELECT (lib.urn_prefix_array($1,$2,$3,true))[1] -- only the last
$wrap$ language SQL IMMUTABLE;
CREATE FUNCTION lib.urn_prefix(
text,
p_len int DEFAULT 3,
p_rm_regex text default '' -- '^br;'
) RETURNS text AS $wrap$
SELECT array_to_string( lib.urn_prefix_array(
CASE WHEN p_rm_regex>''
THEN regexp_replace($1,p_rm_regex,'') ELSE $1
END,
$2,$3), ':')
$wrap$ language SQL IMMUTABLE;
--
/**
* Transforms a string into InitCap of each part, using separator as reference.
* Example: 'hello my.best-friend' with ' -' sep will be 'Hello My.best-Friend'.
* No other change in the string. Uses '_*reserv__' as reserved word.
* Se also ltxtquery() for @ comparing.
*/
CREATE FUNCTION lib.initcap_sep(text,text DEFAULT '\s\.:;,') RETURNS text AS $f$
SELECT array_to_string(array_agg(initcap(x)),'')
FROM regexp_split_to_table(
regexp_replace($1, '(['||$2||']+)', '_*reserv__\1','g'),
'_\*reserv__'
) t(x)
$f$ language SQL IMMUTABLE;
/**
* Transforms an URN LEX into a label-tree (ltree datatype) of its prefix.
* Use p_len=0 to no-prefix reduction. The path is defined by p_sep separators.
* See https://www.postgresql.org/docs/current/static/ltree.html
* and http://patshaughnessy.net/2017/12/14/manipulating-trees-using-sql-and-the-postgres-ltree-extension
*/
CREATE FUNCTION lib.urn_to_ltree(
p_urn text, -- the URN
p_len int DEFAULT 3, -- lenght of the URN prefix, ex. len('a:b:c')=3
p_sep text DEFAULT ';:', -- separators (regex-escaped) that define URN-path
p_chg text DEFAULT '_' -- '_' to URN and '_&' to query
) RETURNS text AS $f$
SELECT rtrim(left( regexp_replace(x, '[~@!].+$', ''), 255 ), '._')
FROM (
SELECT regexp_replace(
array_to_string( array_agg(translate(x,'-.',p_chg)), '.' ),
'\.(\d\d\d\d)_(\d\d)_(\d\d)',
'.\1.\2.\3'
)
FROM regexp_split_to_table(
lib.initcap_sep( CASE WHEN $2=0 THEN $1 ELSE lib.urn_prefix($1,$2) END, '\.:;,' ),
'['||$3||']'
) t2(x)
) t3(x)
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.name2lex(
-- usar unaccent(x) e [^\w], antes convertendo D'Xx para Xx e preservando data-iso com _x_
p_name text
,p_normalize boolean DEFAULT true
,p_cut boolean DEFAULT true
,p_flag boolean DEFAULT false -- unaccent flag
) RETURNS text AS $f$
SELECT CASE WHEN p_flag THEN urn ELSE urn END
FROM (
SELECT trim(replace(
regexp_replace(
CASE WHEN p_normalize THEN lib.normalizeterm2($1,p_cut) ELSE $1 END,
E' d[aeo] | d[oa]s | com | para |^d[aeo] | / .+| [aeo]s | [aeo] |[\-\' ]',
'.',
'g'
),
'..',
'.'
),'.')
) t(urn)
$f$ LANGUAGE SQL IMMUTABLE;
/*
* Converts plain text into URN-lt-query.
*/
CREATE FUNCTION lib.urn_ltquery(text) RETURNS ltxtquery AS $f$
SELECT ltxtquery( lib.urn_to_ltree( lib.name2lex($1), 0, ';:', '_&' ) )
$f$ language SQL IMMUTABLE;
-------
/**
* Explode a string into pairs of words, all sequencially possible pairs.
* Falta vincular a array de stop-words, mais correto que usar regex.
*/
CREATE FUNCTION lib.txt_to_pairs(
text,
p_len int DEFAULT 2, -- 1 = 1 só palavra, 2= 2 palavras, 3= 3 palavras
p_stop_words text[] DEFAULT '{a,e,o,as,à,às,os,as,ao,aos,em,da,de,do,para,com,das,dos,no,na,nas}'::text[],
p_stop_regex text DEFAULT '\s*[,;\.]+\s*' -- avoid stop-words here
) RETURNS text[] AS $f$
DECLARE
list text[]; -- input
pairs text[]; ----to return
anterior1 text;
anterior2 text;
m text;
BEGIN
list := array_except( -- to remove stop-words
string_to_array(
trim(
regexp_replace(
regexp_replace($1,p_stop_regex,' ','g')
,'\s\s+',' ','g')
),
' '
),
CASE WHEN p_stop_words IS NULL THEN array[]::text[] ELSE p_stop_words END
);
pairs:=array[]::text[];
FOREACH m IN ARRAY list LOOP
IF p_len=1 THEN -- 1 só palavra. RETURN list;
pairs := array_append(pairs,m);
ELSE -- multipalavras:
IF anterior1 IS NOT NULL THEN
IF p_len=2 THEN
pairs := array_append(pairs,anterior1||' '||m);
ELSEIF anterior2 IS NOT NULL THEN
pairs := array_append(pairs,anterior2||' '||anterior1||' '||m);
END IF;
anterior2 := anterior1;
END IF;
anterior1 := m;
END IF; -- multi
END LOOP; -- for m
return pairs;
END;
$f$ language PLpgSQL; -- ?? ou só IMMUTABLE? strict IMMUTABLE
---- XML lib
CREATE FUNCTION lib.xpath_byid(
-- ex. lib.xpath_byid(content,'art22','/label')
p_content xml,
p_intra_id text, -- when p_xquery is null, here is a xpath
p_xquery text DEFAULT '' -- avoid NULL
) RETURNS xml[] AS $f$
SELECT CASE
WHEN p_xquery IS NULL THEN xpath($2, p_content)
ELSE xpath( concat('//*[@id="',p_intra_id,'"]',p_xquery), p_content )
END
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.xpath_byparent(
-- ex. lib.xpath_byparent(content,'art22','/*[1]/label[1]|/label[1]')
p_content xml,
p_intra_id text,
p_xquery text DEFAULT '', -- avoid null
p_root xml DEFAULT 'root'::xml, -- the @id for root
p_stop int DEFAULT 0,
p_maxtoroot int DEFAULT 3 -- use 2..4, and obligate use of IDs at sections..
) RETURNS xml[] AS $f$
SELECT CASE
WHEN x::text[] ='{}'::text[] THEN CASE
WHEN p_stop<p_maxtoroot THEN lib.xpath_byparent($1,$2,$3,$4,p_stop+1)
ELSE array[p_root]
END
ELSE x
END
FROM (
SELECT CASE
WHEN p_xquery IS NULL THEN xpath($2, p_content)
ELSE xpath(
concat('//*[@id="',p_intra_id,'"]/..',CASE WHEN p_stop>0 THEN '/..' ELSE '' END,p_xquery),
p_content
)
END as x
) t
$f$ language SQL IMMUTABLE;
CREATE FUNCTION lib.xpath_byparent_1txt(xml,text,text DEFAULT '') RETURNS text AS $wrap$
-- ex. xpath_byparent_1txt(content,'art22','/*[1]/label[1]/text() | /label[1]/text()')
SELECT (lib.xpath_byparent($1,$2,$3))[1]::text
$wrap$ language SQL IMMUTABLE;
--- JSONb
------------------
------------------
------ JSON functions
/**
* set or replace an JSON object.
* Convention: any original array, string and number are setted to "_val" object.
*/
CREATE FUNCTION lib.set_obj(
p_original JSONb,
p_new JSONb
) RETURNS JSONb AS $f$
SELECT j || $2
FROM (SELECT jsonb_typeof($1)) t1(tp), LATERAL (SELECT CASE
WHEN $1 IS NULL THEN '{}'::JSONb
WHEN tp='array' THEN jsonb_build_object('_list',$1)
WHEN tp='number' OR tp='string' OR tp='null' THEN jsonb_build_object('_val',$1)
ELSE $1
END) t2(j)
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION lib.set_kx(
p_original JSONb,
p_kx JSONb
) RETURNS JSONb AS $f$
SELECT lib.set_obj( p_original, jsonb_build_object('_kx',p_kx) )
$f$ LANGUAGE SQL IMMUTABLE;
/**
* Check if json-value is the labelled type.
*/
CREATE FUNCTION lib.check_is(text,JSONb) RETURNS boolean AS $f$
SELECT CASE
WHEN $1='neg' AND ($2#>>'{}')::float<0.0 THEN true
WHEN $1='zero' AND ($2#>>'{}')::float=0 THEN true
WHEN $1='null' AND ($2 IS NULL OR jsonb_typeof($2)='null') THEN true
WHEN $1='emp' AND ($2 IS NULL OR jsonb_typeof($2)='null' OR $2='{}'::jsonb OR ($2#>>'{}')='' OR $2='[]'::jsonb) THEN true
ELSE false
END
$f$ LANGUAGE SQL IMMUTABLE;
--- falta FUNCTION lib.check_is(text,JSON) RETURNS boolean AS $wrap$...
CREATE FUNCTION lib.junpack(
--
-- Remove a sub-object and merge its contents.
-- Ex. SELECT lib.junpack('{"x":12,"sub":{"y":34}}'::jsonb,'sub');
--
JSONB, -- full object
text -- pack name
) RETURNS JSONB AS $f$
SELECT ($1-$2)::JSONB || ($1->>$2)::JSONB
$f$ LANGUAGE SQL IMMUTABLE;
/**
* CAST two arrays as pairs into json-object.
*/
CREATE FUNCTION lib.to_jsonb(
text[], -- 1. the result keys
anyarray -- 2. the result values
) RETURNS JSONB AS $f$
SELECT jsonb_object_agg(k,v)
FROM (SELECT unnest($1), unnest($2)) t(k,v)
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION lib.to_texts( JSONb ) RETURNS text[] AS $f$
SELECT array_agg(x)
FROM jsonb_array_elements_text($1) t(x)
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION lib.to_texts( JSON ) RETURNS text[] AS $f$
SELECT array_agg(x)
FROM json_array_elements_text($1) t(x)
$f$ LANGUAGE SQL IMMUTABLE;
/**
* CAST single value or key-value to json or to json-object.
* Orthonal complement to lib.to_jsonb().
*/
CREATE FUNCTION lib.to_jsonb(
text -- 1. key
anyelement -- 2. value
) RETURNS JSONB AS $f$
SELECT CASE
WHEN $1 IS NULL OR $1='' THEN to_jsonb($2)
ELSE jsonb_build_object($1,$2)
END
$f$ LANGUAGE SQL IMMUTABLE;
------------------
------ Bag functions and configuration-utils
\echo '------- step01-2/utils ------------------'
/**
* For non-null objects in a default merge,
* When the new is null, use the default.
*/
CREATE FUNCTION lib.preserve_notnull(
JSONB, -- default object (only key-value pairs)
JSONB, -- new object, that can replace some key-values
boolean DEFAULT true -- normal flag. When false, returns only the $2 keys.
) RETURNS JSONB AS $f$
SELECT CASE
WHEN $2 IS NULL AND $3 THEN $1
WHEN $1 IS NULL OR $1='{}'::jsonb THEN $2
ELSE (
SELECT jsonb_object_agg(
key,
CASE WHEN jsonb_typeof(value)='null' THEN $1->key ELSE value END
)
FROM jsonb_each($1||$2) t
WHERE CASE WHEN $3 THEN true ELSE $2->key IS NOT NULL END
)
END
$f$ LANGUAGE SQL IMMUTABLE;
------
---
-- AGGREGATORS:
/**
* Concatenate strings ignoring NULLs and empties.
*/
CREATE or replace FUNCTION lib.concat_notnull(
text, -- first string to concat
text -- first string to concat
--,p_sep text DEFAULT ' ' -- separator
) RETURNS text AS $f$
SELECT CASE
WHEN $1 IS NULL OR $1='' THEN $2
WHEN $2 IS NULL OR $2='' THEN $1
ELSE $1 || ' ' || $2
END;
$f$ immutable language SQL;
CREATE AGGREGATE lib.concat_notnull_agg(
basetype = text,
sfunc = lib.concat_notnull, -- bug postgresql not accept default sep
stype = text,
initcond = ''
);
---- specialized schemas
CREATE or replace FUNCTION bag.cat(jsonb,jsonb) RETURNS JSONb AS $f$
-- only to use with bag.agg();
SELECT $1||$2
$f$ language SQL IMMUTABLE;
/**
* Bag aggregator. Works fine also as generic jsonb_agg_object_cat().
* Like jsonb_object_agg(), but use existent pairs.
*/
CREATE AGGREGATE bag.agg (jsonb) (
sfunc = bag.cat,
stype = jsonb,
initcond = '{}'
);
-- test with SELECT bag.agg(x) FROM (VALUES ('{"a":1.0}'::jsonb) , ('{"x":[null, "d"]}'::jsonb)) t(x);
------
CREATE or replace FUNCTION lib.array_pop_off(ANYARRAY) RETURNS ANYARRAY AS $f$
SELECT $1[2:array_length($1,1)];
$f$ LANGUAGE sql IMMUTABLE;
CREATE or replace FUNCTION lib.json_array_castext(json) RETURNS text[] AS $f$
SELECT array_agg(x)
FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;
CREATE or replace FUNCTION lib.url_tocmp(url text) RETURNS text AS $f$
SELECT lower(trim( regexp_replace($1,'^https?://(www.)?','') , '/ '));
$f$ LANGUAGE sql IMMUTABLE;
--- agg
/**
* Custom array_agg() for NOT NULL.
* @see https://stackoverflow.com/a/17303965
* @see http://ejrh.wordpress.com/2011/09/27/denormalisation-aggregate-function-for-postgresql/
*/
CREATE OR REPLACE FUNCTION fn_array_agg_notnull (
a anyarray
, b anyelement
) RETURNS ANYARRAY
AS $$
BEGIN
IF b IS NOT NULL THEN
a := array_append(a, b);
END IF;
RETURN a;
END;
$$ IMMUTABLE LANGUAGE 'plpgsql';
DROP AGGREGATE IF EXISTS array_agg_notnull(ANYELEMENT);
CREATE AGGREGATE array_agg_notnull(ANYELEMENT) (
SFUNC = fn_array_agg_notnull,
STYPE = ANYARRAY,
INITCOND = '{}'
);
----------------------
CREATE or replace FUNCTION jsonb_merge_sum( jsonb, jsonb ) RETURNS jsonb AS $f$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 || (
SELECT jsonb_object_agg( key, value::int + COALESCE(($1->>key)::int,0) )
FROM jsonb_each_text($2)
) END
$f$ language SQL IMMUTABLE;
-- INCLUIR DEMAIS array_* de https://postgres.cz/wiki/Array_based_functions e similares
CREATE or replace FUNCTION array_intersect(anyarray, anyarray) RETURNS anyarray AS $f$
SELECT ARRAY(SELECT unnest($1)
INTERSECT
SELECT unnest($2))
$f$ LANGUAGE sql IMMUTABLE;
CREATE or replace FUNCTION array_subtraction(anyarray, anyarray) RETURNS anyarray AS $f$
SELECT ARRAY(SELECT unnest($1)
EXCEPT
SELECT unnest($2))
$f$ LANGUAGE sql IMMUTABLE;
CREATE or replace FUNCTION jsonb_select_keys(jsonb, text[]) RETURNS jsonb AS $f$
SELECT jsonb_object_agg(k,$1->k)
FROM (SELECT jsonb_object_keys($1) INTERSECT SELECT unnest($2)) t(k)
$f$ LANGUAGE sql IMMUTABLE;
CREATE or replace FUNCTION array_distinct_sort (
ANYARRAY,
p_no_null boolean DEFAULT true
) RETURNS ANYARRAY AS $f$
SELECT CASE WHEN array_length(x,1) IS NULL THEN NULL ELSE x END -- same as x='{}'::anyarray
FROM (
SELECT ARRAY(
SELECT DISTINCT x
FROM unnest($1) t(x)
WHERE CASE
WHEN p_no_null THEN x IS NOT NULL
ELSE true
END
ORDER BY 1
)
) t(x)
$f$ language SQL strict IMMUTABLE;
CREATE or replace FUNCTION array_merge_sort(
ANYARRAY, ANYARRAY, boolean DEFAULT true
) RETURNS ANYARRAY AS $wrap$
SELECT array_distinct_sort(array_cat($1,$2),$3)
$wrap$ language SQL IMMUTABLE;
DROP AGGREGATE IF EXISTS array_agg_mult(anyarray);
CREATE AGGREGATE array_agg_mult(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}'
);
--------------
-----------
--- ENCODE/DECODE
CREATE or replace FUNCTION bigint_base_decode(
p_val text,
p_base int, -- 16 to 36
p_alphabet text = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
) RETURNS bigint AS $f$
SELECT SUM(
( p_base^(length($1)-i) )::bigint
* -- base^j * digit_j
( strpos(p_alphabet,d) - 1 )
)::bigint
FROM regexp_split_to_table($1,'') WITH ORDINALITY t1(d,i)
$f$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE or replace FUNCTION bigint_base_decode(
p_val text,
p_alphabet text = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
) RETURNS bigint AS $wrap$
SELECT bigint_base_decode($1,length($2),$2)
$wrap$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION bigint_base_encode(
p_val bigint,
p_base int = 32, -- 2 to 36
p_min_width int = 0, -- -1 cuts and >0 put zeros
p_alphabet char[] = ARRAY[ -- expand to 64
'0','1','2','3','4','5','6','7','8','9',
'A','B','C','D','E','F','G','H','I','J',
'K','L','M','N','O','P','Q','R','S','T',
'U','V','W','X','Y','Z'
]
) RETURNS text AS $f$
DECLARE
ret text := '';
BEGIN
-- p_val := abs(p_val) or
IF p_val < 0 THEN RETURN NULL; END IF;
WHILE p_val != 0 LOOP
ret := p_alphabet[(p_val % p_base)+1] || ret;
p_val := p_val / p_base;
END LOOP;
IF ret='' THEN ret=p_alphabet[1]; END IF; -- gambi
IF p_min_width > 0 AND length(ret) < p_min_width THEN
RETURN lpad(ret, p_min_width, p_alphabet[1]);
ELSEIF p_min_width < 0 AND length(ret) > 1 THEN
RETURN ltrim(ret,p_alphabet[1]);
ELSE
RETURN ret;
END IF;
END;
$f$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION bigint_base_encode(
p_val bigint,
p_base int, -- 16 to 36
p_min_width int,
p_alphabet text -- e.g. '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
) RETURNS text AS $wrap$
SELECT bigint_base_encode($1,$2,$3,regexp_split_to_array($4,''))
$wrap$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION bigint_base_encode(
p_val bigint,
p_alphabet text='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
p_min_width int=0
) RETURNS text AS $wrap$
SELECT bigint_base_encode($1,length($2),$3,$2)
$wrap$ LANGUAGE SQL IMMUTABLE STRICT;
--------
---- falta case dando nomes, por exemplo b58btc=base 58 do Bitcoin, b32ghs é a base32 do Geohash
---- ... b64rfc e b32rfc são as representações padrão b64std, b32std, assim como b16std e b10std são todas bem conhecidas.
--- por hora:
CREATE or replace FUNCTION bigint_geohash_decode(
p_val text
) RETURNS bigint AS $wrap$
SELECT bigint_base_decode(substr($1,1,12),32,'0123456789bcdefghjkmnpqrstuvwxyz')
-- max = select bigint_geohash_encode(9223372036854775800)
$wrap$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE or replace FUNCTION bigint_geohash_encode(
p_val bigint
) RETURNS text AS $wrap$
SELECT bigint_base_encode($1,'0123456789bcdefghjkmnpqrstuvwxyz')
$wrap$ LANGUAGE SQL IMMUTABLE STRICT;
----
CREATE or replace FUNCTION bigint_geohash_b4tob32(
p_val text
) RETURNS text AS $wrap$
SELECT bigint_geohash_encode( bigint_base_decode($1,'0123') )
$wrap$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE or replace FUNCTION bigint_geohash_b32tob4(
p_val text
) RETURNS text AS $wrap$
SELECT bigint_base_encode(bigint_geohash_decode($1), '0123', -1)
$wrap$ LANGUAGE SQL IMMUTABLE STRICT;
----
-- Exemplo de uso:
COPY (
SELECT *, substr(geohash,6) geoash_local,
bigint_geohash_b4tob32(
substr( geohash_b4, CASE WHEN substr(geohash,1,6)='6gkzjg' THEN 17 ELSE 15 END )
) clp_local
FROM (
SELECT *, bigint_geohash_b32tob4(geohash) geohash_b4
FROM (
SELECT gid,tipo,id,poi,eco, st_geohash(geom,9) geohash
FROM teto_pr.edificacoes
) t1
) t2
ORDER BY geohash
) TO '/tmp/everton.csv' CSV HEADER
;