Skip to content

Gerando a documentação

Jump to bottom
Peter edited this page Nov 1, 2022 · 13 revisions

Ver também test long documentation, /docs e digital-guard/preserv/issue 84... In 2022-10 at https://github.com/AddressForAll/pg_pubLib-v1/blob/main/src/pgdoc-step1-ini.sql

PS: ver schema pg_catalog para as funções nativas.

Principal

Um conjunto de filtros mais específicos é aplicado. O grep abaixo ajuda a obter as regular expressions.

grep -v COMMENT *.sql | grep FUNCTION 
# after all generation:
sudo chmod 766 /tmp/*.md
DELETE FROM pgdoc.selected_docs;

WITH pubf AS (
  SELECT * 
  FROM pgdoc.doc_UDF_show_simplelines_asXHTML('public','','^(ST_|_st_|geometry_|_postgis_)')
  WHERE j->>'language' IN ('sql', 'plpgsql', 'plpython3u') AND j->>'prokind' IN ('func', 'agg')
)
INSERT INTO pgdoc.selected_docs
    SELECT 'misc.md', 'general', *
    FROM pubf WHERE j->>'name' ~* '^(iIF|ROUND|trunc|decimal)'
  UNION ALL
    SELECT 'array.md', 'array', *
    FROM pubf WHERE j->>'name' ~* '^(pg_csv_head|array_last|jsonb_to_|array_|unnest_|bigint2d_|base36_|ROUND)'
  UNION ALL
    SELECT 'string.md', 'python', *
    FROM pubf WHERE j->>'name' ~* '^(py3_|str_to_normalized|yaml_to_jsonb|jsonb_to_yaml|file_exists|jsonb_mustache_render|yamlfile_to_jsonb|hcodes_common_prefix)'
  UNION ALL
    SELECT 'string.md', 'scalar', *
    FROM pubf WHERE j->>'name' ~* '^(to_bigint|to_integer|to_hex|stragg_prefix|str_abbrev_|str_urldecode|lexname_)'
  UNION ALL
    SELECT 'misc.md', 'admin', *
    FROM pubf WHERE j->>'name' ~* '^(pg_relation_lines|volat_file_write|pg_tablestruct_dump|jsonb_pg_stat|sql_parse_|doc_UDF_)'
  UNION ALL
    SELECT 'json.md', 'misc', *
    FROM pubf WHERE j->>'name' ~* '^(jsonb_objslice|jsonb_object_keys_as|jsonb_strip_nulls|jsonb_object_len|jsonb_rename|jsonb_summable|jsonb_pretty|csv_to_jsonb)'
  UNION ALL
    SELECT 'string.md', 'aggregate', *
    FROM pubf WHERE j->>'name' ~* '^(stragg_prefix)'
  UNION ALL
    SELECT 'hcode.md', 'Distrib', *
    FROM pubf WHERE j->>'name' ~* '^(hcode_prefixset|geocode_distribution|hcode_distribution|hcode_signature)'
  UNION ALL
    SELECT 'hcode.md', 'Encode-Decode', *
    FROM pubf WHERE j->>'name' ~* '^(varbit_to_int|vbit_to_baseh|baseh_|str_ggeohash_|str_geohash_|str_geouri)'
;
-- select file, grlabel, jinfo->>'name' as name from pgdoc.selected_docs;
select pgdoc.doc_UDF_showselected_asMD_file();

Outros

-- all public UDF functions:
SELECT volat_file_write( '/tmp/lix00gen.md',
  pgdoc.doc_UDF_show_simple_asXHTML( 'public', '', 'ST_', false)::text 
);

By prefix:

-- 00 general:
SELECT volat_file_write( '/tmp/lix00gen.md',
  pgdoc.doc_UDF_show_simple_asXHTML( 'public', '^(iif|round|round|minutes|trunc_bin)$', '', false)::text 
);
-- 01 array:
SELECT volat_file_write( '/tmp/lix01arr.md',
  pgdoc.doc_UDF_show_simple_asXHTML( 'public', '^(pg_csv_head|pg_csv_head_tojsonb|jsonb_to_bigints|jsonb_to_bigints|unnest_2d_1d|array_.+)$', '', false)::text 
);
-- 01 python-string
SELECT volat_file_write( '/tmp/lix01py.md',
  pgdoc.doc_UDF_show_simple_asXHTML( 'public', '^(py3_return_version|str_to_normalized_unicode|yaml_to_jsonb|jsonb_to_yaml|file_exists|jsonb_mustache_render|yamlfile_to_jsonb)$', '', false)::text 
);
-- 02 string:
SELECT volat_file_write( '/tmp/lix02str.md',
  pgdoc.doc_UDF_show_simple_asXHTML( 'public', '^(to_bigint|to_integer|to_hex|stragg_prefix|str_urldecode|str_abbrev.+)$', '', false)::text 
);
-- 03-admin
-- ...

INSERT INTO pgdoc.assert(udf_pubid,assert_group,query,result) VALUES
  ('cbc3bb', 'general', 'SELECT iif(0=1,1,2)', '2' ),
  ('cbc3bb', 'general', E'SELECT iif(0=0,\'Hello\'::text,\'Bye\')', 'Hello' ) 
  -- ...
;

Convêm estruturar tudo isso nos scripts com target no makefile (tarefa pendente!).

A construção final do documento (copia/cola das tabelas) é artezanal, mas as tabelas não podem ser alteradas manualmente. Havendo necessidade, criar seções específicas para comentar exemplos ou descrever mais detalhes sobre as funções. Os ASSERTS continuam sendo um importante ativo neste processo... O parser será outra importante ferramenta.

Resultado

00 general

Function / Description / Example
iif(boolean, anyelement, anyelement) → anyelement

Immediate IF. Sintax sugar for the most frequent CASE-WHEN. Avoid with text, need explicit cast.

SELECT iif(0=1,1,2)
SELECT iif(0=0,'Hello'::text,'Bye')
round(double precision, integer) → numeric

Cast for ROUND(float,x). Useful for SUM, AVG, etc. See also https://stackoverflow.com/a/20934099/287948.
round(double precision, double precision) → float8

ROUND by accuracy. See Round9 at https://stackoverflow.com/a/20933882/287948
trunc_bin(bigint, integer) → int8

01 array

Function / Description / Example
array_agg_cat(anyarray) → anyarray
array_agg_cat_distinct(anyarray) → anyarray
array_cat_distinct(anyarray, anyarray) → anyarray
array_distinct_sort(anyarray, boolean) → anyarray
array_fastsort(anyarray) → anyarray
array_fillto(anyarray, integer, anyelement) → anyarray
array_fillto_duo(anyarray, anyarray, anyelement, anyarray, anyarray) → record
array_is_allsame(anyarray) → bool
array_last(anyarray) → anyelement
array_last_butnot(anyarray, anyarray) → anyelement
array_last_butnot(anyarray, anyelement) → anyelement
array_merge_sort(anyarray, anyarray, boolean) → anyarray
array_reduce_dim(anyarray) → anyarray
array_sample(anyarray, integer) → anyarray
array_subtract(anyarray, anyarray, boolean) → anyarray
jsonb_to_bigints(jsonb) → _int8

Casts JSON array of non-floating numbers to SQL array of bigints.
pg_csv_head(text, text, bigint) → _text

Devolve array do header de um arquivo CSV com separador estrito, lendo apenas primeiros bytes.
pg_csv_head_tojsonb(text, boolean, text, bigint, boolean) → jsonb
unnest_2d_1d(anyarray, anyarray) → anyarray

python

Function / Description / Example
file_exists(text) → bool
jsonb_mustache_render(text, jsonb, text) → text
jsonb_to_yaml(text) → text
py3_return_version() → text
str_to_normalized_unicode(text) → text
yaml_to_jsonb(text) → jsonb
yamlfile_to_jsonb(text) → jsonb

02 string

Function / Description / Example
str_abbrev_minscore(text, text, text, text) → text
str_abbrev_regex(text) → text
str_urldecode(text) → text
stragg_prefix(text, ARRAY, text) → text
to_bigint(text) → int8
to_hex(ARRAY, integer) → _text
to_integer(text) → int4
Clone this wiki locally