Consultas diretas no dataset.big

Abaixo OLD SCRIPT

CREATE SCHEMA lex; -- for URN LEX

CREATE TYPE lex.voc AS (
  id int,
  about text,
  prefLabel text,
  altLabel text,
  broader text,
  facetaAcronimo text,
  faceta text
);


/**
ERRO usar direto big.c
SELECT (big.c ->> 0)::integer AS id,
   big.c ->> 1 AS about,
   big.c ->> 2 AS preflabel,
   big.c ->> 3 AS altlabel,
   big.c ->> 4 AS broader,
   big.c ->> 5 AS facetaacronimo,
   big.c ->> 6 AS faceta
  FROM dataset.big
 WHERE big.source = dataset.idconfig('tipodocumento'::text)
 ORDER BY ((big.c ->> 0)::integer);

*/
CREATE or replace FUNCTION dataset.select_vwvoc(p_vocname text) RETURNS setof lex.voc  AS $f$
  -- revisar se pega daqui ou do tabelão dataset.big.c direto e com labels corretos
  -- ... revisar todo o processo de conversão e atualização. Falta talvez uma biblioteca
  --   de update ou redo do big.c a partir da view.
  SELECT * FROM   dataset.vw_localidade WHERE $1='localidade'
  UNION
  (SELECT * FROM   dataset.vw_autoridade WHERE $1='autoridade')
  UNION
  (SELECT * FROM   dataset.vw_tipodocumento WHERE $1='tipodocumento')
  --ORDER BY about;
$f$ LANGUAGE SQL IMMUTABLE;


CREATE or replace FUNCTION lex.abbrev_urn(p_broader text,p_val text) RETURNS text  AS $f$
  SELECT translate( COALESCE(lib.name2lex($1)||';','') || lib.name2lex($2) , '#', '');
$f$ LANGUAGE SQL IMMUTABLE;






/**
 * JSON vocabulary (sql view representation from CSV) to JSON-LD
 */
CREATE or replace FUNCTION lex.voc_big2jld(JSONB) RETURNS setof JSONB  AS $f$

SELECT (big.c ->> 0)::integer AS id,
   big.c ->> 1 AS about,
   big.c ->> 2 AS "prefLabel",
   big.c ->> 3 AS "altLabel",
   big.c ->> 4 AS broader,
   big.c ->> 5 AS "facetaAcronimo",
   big.c ->> 6 AS faceta
  FROM dataset.big
 WHERE big.source = dataset.idconfig($1);
 -- ORDER BY ((big.c ->> 0)::integer);


SELECT jsonb_build_object(
    '@type',  'skos:Concept'
    ,'rdf:id', c->0
    ,'rdf:about', c->1
    ,'skos:prefLabel',
       jsonb_build_object(
         '@language',c->>'lang',
         '@value',   c->3
         '@lexml:facetaAcronimo', c->5
         '@lexml:faceta',         c->6
       )
  ) || (SELECT CASE WHEN $1->>'broader' IS NOT NULL
    THEN jsonb_build_object('skos:broader',jsonb_build_object('rdf:resource',$1->>'broader'))
    ELSE '{}'::JSONb END
  ) || (SELECT CASE WHEN $1->>'altlabel' IS NOT NULL
    THEN jsonb_build_object('skos:altLabel',jsonb_build_object(  -- forcing to be acronym
      '@language','acronym',
      '@value',   $1->>'altlabel',
      '@lexml:urnAlt', lex.abbrev_urn($1->>'broader',$1->>'altlabel')
    )) ELSE '{}'::JSONb END
  );
$f$ LANGUAGE SQL IMMUTABLE;


/**
 * JSON vocabulary (tabular representation from CSV) to JSON-LD
 */
CREATE or replace FUNCTION lex.voc_tojld(JSONB) RETURNS JSONB  AS $f$
SELECT jsonb_build_object(
    '@type',  'skos:Concept'
    ,'rdf:id',    ($1->>'id')::int
    ,'rdf:about', $1->>'about'
    ,'skos:prefLabel',
       jsonb_build_object(
         '@language',$1->>'lang',
         '@value',   $1->>'preflabel',
         '@lexml:facetaAcronimo', $1->>'facetaacronimo',
         '@lexml:faceta',         $1->>'faceta'
       )
  ) || (SELECT CASE WHEN $1->>'broader' IS NOT NULL
    THEN jsonb_build_object('skos:broader',jsonb_build_object('rdf:resource',$1->>'broader'))
    ELSE '{}'::JSONb END
  ) || (SELECT CASE WHEN $1->>'altlabel' IS NOT NULL
    THEN jsonb_build_object('skos:altLabel',jsonb_build_object(  -- forcing to be acronym
      '@language','acronym',
      '@value',   $1->>'altlabel',
      '@lexml:urnAlt', lex.abbrev_urn($1->>'broader',$1->>'altlabel')
    )) ELSE '{}'::JSONb END
  );
$f$ LANGUAGE SQL IMMUTABLE;

/**
 * Returns a LexML vocabulary in JSON-LD format.
 * USO:  COPY (SELECT jsonb_pretty(j) FROM lex.voc_tojsonld('autoridade') t(j)) TO '/tmp/test.json';
 *       sed 's/\\n/\n/g' < /tmp/test.json > data-cache/autoridade.json
 */
CREATE or replace FUNCTION lex.voc_tojsonld(p_vocname text) RETURNS JSONb AS $f$
  SELECT '{"@context":{
        "dc":"http://purl.org/dc/elements/1.1/",
        "dcterms":"http://purl.org/dc/terms/",
        "lexml":"http://www.lexml.gov.br/voc/br/",
        "rdf":"http://www.w3.org/1999/02/22-rdf-syntax-ns#",
        "rdfs":"http://www.w3.org/2000/01/rdf-schema#",
        "skos":"http://www.w3.org/2008/05/skos#",
        "xsd":"http://www.w3.org/2001/XMLSchema#"
        }}'::JSONb  || jsonb_build_object('@graph',(
          SELECT to_jsonb(array_agg(jline))
          FROM (
            SELECT lex.voc_tojld(to_jsonb(t1)) AS jline
            FROM dataset.select_vwvoc($1) t1
            ORDER BY id -- entrega ordenado? comparar... e decidir se usa id
          ) t2
        ));
$f$ LANGUAGE SQL IMMUTABLE;