Read, write, validate and edit MARC 21 bibliographic data (ISO 2709, MARCXML, MARC-in-JSON, breaker, Aleph; UTF-8 and MARC-8)
Maintainer(s):
mgbilby
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL marc21 FROM community;
LOAD marc21;
Example
-- 1. Read a binary MARC dump (gzip and globs work; MARC-8 and UTF-8
-- are detected per record and decoded to NFC Unicode)
SELECT control_number,
marc_subfield(fields, '245', 'a') AS title,
marc_subfields(fields, '650', 'a') AS subjects
FROM read_marc('catalogue/*.mrc.gz');
-- 2. One row per subfield, for frequency and completeness work
SELECT tag, code, count(*) FROM read_marc_subfields('dump.mrc')
GROUP BY ALL ORDER BY 3 DESC;
-- 3. MARCXML, MARC-in-JSON (incl. FOLIO SRS shape), breaker, Aleph
SELECT * FROM read_marcxml('export.xml');
SELECT * FROM read_marcjson('records.ndjson');
-- 4. Address by MARCspec; validate structurally or against Avram
SELECT marc_spec(leader, fields, '008/35-37') FROM read_marc('dump.mrc');
SELECT marc_validate(leader, fields) FROM read_marc('dump.mrc');
-- 5. Batch-edit and write back — full encoding crosswalk
COPY (SELECT marc_set_subfield(fields, '040', 'a', 'XX-XxUND') AS fields,
leader
FROM read_marc('in.mrc'))
TO 'out.mrc' (FORMAT marc, ENCODING 'marc8');
-- 6. Or write MARCXML / breaker / MARC-in-JSON
COPY (SELECT * FROM read_marc('in.mrc')) TO 'out.xml' (FORMAT marcxml);
About marc21
A cleanly licensed MARC 21 toolkit for DuckDB, aimed at cataloging and metadata librarians: the parser, MARC-8 tables and writer are implemented from ISO 2709 and the Library of Congress specifications only, not from previous software, and verified by differential fuzzing and round-trip testing.
Read
read_marc (nested LIST(STRUCT) per record), read_marc_subfields
(one row per subfield), read_marc_raw (original bytes + error column),
read_marcxml, read_marc_breaker, read_marcjson (community shape,
NDJSON and FOLIO SRS envelopes), read_alephseq. Glob patterns, gzip,
and DuckDB FileSystem protocols (httpfs URLs — including SRU and OAI-PMH
responses, whose envelopes are handled). read_z3950 retrieves records
live from Z39.50 servers (Library of Congress, OCLC, most ILSes) with a
built-in dependency-free client. On DuckDB v2.0 the readers are
multi-file readers (lists of paths, filename and file_index columns,
union_by_name, hive_partitioning, parallel across files). Scans
parallelise across and within files.
Write
COPY ... TO (FORMAT marc | marcxml | mrk | marcjson). ISO 2709 output
in UTF-8 or MARC-8 (full LC code tables: ANSEL, Cyrillic, Greek, Hebrew,
Arabic, EACC with escape designations; NCR fallback), round-trip tested.
Query, validate, edit
MARCspec addressing (marc_spec), structural and Avram-schema validation
(marc_validate, marc_validate_avram), format detection, record
editing (marc_set_subfield, marc_remove_fields, marc_replace_values,
…), merge with field protection (marc_merge), record diff
(marc_diff), identifier normalisation (marc_isbn13, marc_issn,
marc_lccn, marc_oclc), NACO normalisation and match keys for
deduplication, and a FOLIO Source Record Storage bridge
(marc_parse_json) for in-place analytics on an ATTACHed FOLIO database.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| marc_008_books | macro | Helper for marc_008_struct: returns the Books positions 008/18-34 (illustrations, audience, form, nature_of_contents, government_publication, conference, festschrift, index, literary_form, biography) as a STRUCT | NULL | [marc_008_books('850101s1985 nyua 000 1 eng d')] |
| marc_008_parts | macro | Helper for marc_008_struct: returns the all-materials 008 positions (dates, place, language, modified, cataloging_source) as a STRUCT, with a books sub-struct when is_books is true | NULL | [marc_008_parts('850101s1985 nyua 000 1 eng d', true)] |
| marc_008_struct | macro | Returns the record's 008 as a typed STRUCT of all-materials positions plus a books sub-struct for Leader/06 a/t with Leader/07 a/c/d/m; NULL when there is no 008 | NULL | [SELECT marc_008_struct(leader, fields) FROM read_marc('records.mrc')] |
| marc_260c_copyright_year | macro | Helper for marc_264_from_260: returns the first four-digit copyright year in a 260 $c string (after a copyright or phonogram sign, 'copyright ' or 'c'), or '' when none | NULL | [marc_260c_copyright_year('2002, ©2002.')] |
| marc_264_copyright | macro | Helper for marc_264_from_260: returns a 264 _4 field struct with $c set to the copyright sign plus year from field f's $c, or NULL when no copyright year is present | NULL | [SELECT marc_264_copyright(marc_fields(fields, '260')[1]) FROM read_marc('records.mrc')] |
| marc_264_from_260 | macro | Replaces each 260 in place with an RDA 264 _1 publication field plus a 264 _4 copyright-date field when $c carries a copyright year; $e/$f/$g are not carried over | NULL | [SELECT marc_264_from_260(fields) FROM read_marc('records.mrc')] |
| marc_264_publication | macro | Helper for marc_264_from_260: converts one 260 field struct f to a 264 _1 struct keeping $3 $6 $8 $a $b $c, with copyright dates stripped from $c and empty subfields dropped | NULL | [SELECT marc_264_publication(marc_fields(fields, '260')[1]) FROM read_marc('records.mrc')] |
| marc_86x_zip | macro | Helper for marc_expand_863: pairs a caption field's $a-$f captions with a value field's $a-$f values by code, as LIST(STRUCT(code, cap, val)) | NULL | [SELECT marc_86x_zip(p.caption_field, p.value_field) FROM (SELECT unnest(marc_holdings_pairs(fields)) AS p FROM read_marc('records.mrc'))] |
| marc_add_field | scalar | Inserts one field struct (tag, ind1, ind2, value, subfields) into the fields list in tag order, after existing fields with the same tag; the tag must be a literal 3-character tag | NULL | [SELECT marc_add_field(fields, {tag: '590', ind1: ' ', ind2: ' ', value: NULL, subfields: [{code: 'a', value: 'Local note'}]}) FROM read_marc('records.mrc')] |
| marc_add_local | macro | Builds a data field tag_ with indicators i1/i2 from parallel codes_list and values_list and inserts it into fields in tag order via marc_add_field | NULL | [SELECT marc_add_local(fields, '590', ' ', ' ', ['a'], ['Gift of the author']) FROM read_marc('records.mrc')] |
| marc_alma_bib_body | macro | Renders a record as the |
NULL | [SELECT marc_alma_bib_body(leader, fields) FROM read_marc('records.mrc')] |
| marc_alma_headers | macro | Returns the Alma Bibs API request headers as a MAP: Accept application/xml, plus 'Authorization: apikey …' when apikey is not NULL | NULL | [marc_alma_headers('my-api-key')] |
| marc_alma_put_sql | macro | Builds, without running it, the http_request http_put SQL statement text that updates Alma bib mms_id with bib_body; the output embeds apikey, so treat it as a credential | NULL | [marc_alma_put_sql('https://api-na.hosted.exlibrisgroup.com', '991234567890123', 'my-api-key', ' |
| marc_alma_put_url | macro | Builds the Alma Bibs API update URL for mms_id without sending anything; validate and stale_version_check default to false, optional apikey is appended as a query parameter | NULL | [marc_alma_put_url('https://api-na.hosted.exlibrisgroup.com', '991234567890123', stale_version_check := true)] |
| marc_alma_sql_quote | macro | Helper for marc_alma_put_sql: wraps a string in single quotes with embedded quotes doubled, as a SQL string literal | NULL | [marc_alma_sql_quote('O''Brien')] |
| marc_alma_update_bib | table_macro | Sends a PUT that replaces Alma bib mms_id with bib_body and returns one row (status, content_type, body); needs LOAD http_request at use time and changes the live catalog record; build bib_body with marc_alma_bib_body | NULL | [SELECT * FROM marc_alma_update_bib('https://api-na.hosted.exlibrisgroup.com', '991234567890123', 'my-api-key', ' |
| marc_alma_xml_escape | macro | Helper for marc_alma_bib_body: XML-escapes a string like marc_xml_escape and additionally escapes the apostrophe as ' | NULL | [marc_alma_xml_escape('O''Brien & Sons')] |
| marc_authority_refs | macro | Returns an authority record's 1XX/4XX/5XX fields as LIST(STRUCT(kind, tag, heading)) with kind authorized, see_from or see_also; empty for non-authority leaders | NULL | [SELECT marc_authority_refs(leader, fields) FROM read_marc('records.mrc')] |
| marc_bf_chomp | macro | Helper for marc_bibframe_jsonld: trims a value and strips trailing ISBD separators (whitespace, slash, colon, semicolon, comma); NULL when nothing is left | NULL | [marc_bf_chomp('Rivers of Poland /')] |
| marc_bf_chompd | macro | Helper for marc_bibframe_jsonld: like marc_bf_chomp but also strips trailing periods; NULL when nothing is left | NULL | [marc_bf_chompd('author.')] |
| marc_bf_classes | macro | Helper for marc_bibframe_jsonld: returns bf:ClassificationLcc objects from 050 $a/$b and bf:ClassificationDdc objects from 082 $a, as LIST(VARCHAR) | NULL | [SELECT marc_bf_classes(fields) FROM read_marc('records.mrc')] |
| marc_bf_contrib | macro | Helper for marc_bibframe_jsonld: renders one 1XX/7XX field struct f as a bf:Contribution JSON object with agent class from the tag and role from $4 (relators URI) or $e | NULL | [SELECT marc_bf_contrib(marc_fields(fields, '100')[1]) FROM read_marc('records.mrc')] |
| marc_bf_contribs | macro | Helper for marc_bibframe_jsonld: returns one bf:Contribution JSON object per 100/110/111/700/710/711 data field, as LIST(VARCHAR) | NULL | [SELECT marc_bf_contribs(fields) FROM read_marc('records.mrc')] |
| marc_bf_date | macro | Helper for marc_bibframe_jsonld: returns the first four-digit year in a provision date, else the value with brackets and trailing punctuation removed | NULL | [marc_bf_date('[1998?], c1997.')] |
| marc_bf_id | macro | Helper for marc_bibframe_jsonld: returns the record's trimmed 001 as the node identity stem, or 'record' when there is no 001 | NULL | [SELECT marc_bf_id(fields) FROM read_marc('records.mrc')] |
| marc_bf_ident | macro | Helper for marc_bibframe_jsonld: renders one identifier as a JSON object with @type kind and rdf:value val | NULL | [marc_bf_ident('bf:Isbn', '9780306406157')] |
| marc_bf_idents | macro | Helper for marc_bibframe_jsonld: returns identifier JSON objects for 020 (bf:Isbn), 022 (bf:Issn), 010 (bf:Lccn) normalized when possible, and (OCoLC) 035 values (bf:OclcNumber) | NULL | [SELECT marc_bf_idents(fields) FROM read_marc('records.mrc')] |
| marc_bf_instance | macro | Helper for marc_bibframe_jsonld: renders the BIBFRAME Instance node with @id uri_ || '#instance': title, responsibility, provision, identifiers, extent, media, carrier, electronic locators | NULL | [SELECT marc_bf_instance(leader, fields, marc_bf_id(fields)) FROM read_marc('records.mrc')] |
| marc_bf_lang | macro | Helper for marc_bibframe_jsonld: returns the 008/35-37 language code when it is three lowercase letters, else NULL | NULL | [SELECT marc_bf_lang(fields) FROM read_marc('records.mrc')] |
| marc_bf_obj | macro | Helper for marc_bibframe_jsonld: wraps a list of JSON key:value fragments in braces as one object text, dropping NULL fragments | NULL | [marc_bf_obj([marc_bf_str('bf:mainTitle') || ':' || marc_bf_str('Rivers'), NULL])] |
| marc_bf_provision | macro | Helper for marc_bibframe_jsonld: renders a provision field struct f as a bf:Publication JSON object with place, agent and date; NULL when f is NULL | NULL | [SELECT marc_bf_provision(marc_bf_pubfield(fields)) FROM read_marc('records.mrc')] |
| marc_bf_pubfield | macro | Helper for marc_bibframe_jsonld: returns the provision field struct, preferring 264 with ind2 1, then the first 260, then any 264 | NULL | [SELECT marc_bf_pubfield(fields) FROM read_marc('records.mrc')] |
| marc_bf_sf | macro | Helper for marc_bibframe_jsonld: returns the first value of subfield code_ within the single field struct f, or NULL | NULL | [SELECT marc_bf_sf(marc_fields(fields, '245')[1], 'a') FROM read_marc('records.mrc')] |
| marc_bf_sfjoin | macro | Helper for marc_bibframe_jsonld: joins with sep the punctuation-trimmed values of field f's subfields whose code appears in the string codes_; NULL when empty | NULL | [SELECT marc_bf_sfjoin(marc_fields(fields, '650')[1], 'avxyz', '–') FROM read_marc('records.mrc')] |
| marc_bf_sfvals | macro | Helper for marc_bibframe_jsonld: returns every value of subfield code_ within the single field struct f, as LIST(VARCHAR) | NULL | [SELECT marc_bf_sfvals(marc_fields(fields, '650')[1], 'a') FROM read_marc('records.mrc')] |
| marc_bf_str | macro | Helper for marc_bibframe_jsonld: renders a value as a quoted JSON string literal, escaping backslash, double quote, newline, carriage return and tab | NULL | [marc_bf_str('Rivers of Poland')] |
| marc_bf_subject | macro | Helper for marc_bibframe_jsonld: renders one 6XX field struct f as a bf:Topic, bf:Place or bf:Temporal JSON object with a double-hyphen label and @id from an http $0 | NULL | [SELECT marc_bf_subject(marc_fields(fields, '650')[1]) FROM read_marc('records.mrc')] |
| marc_bf_subjects | macro | Helper for marc_bibframe_jsonld: returns one subject JSON object per 600/610/611/630/648/650/651/653 data field, as LIST(VARCHAR) | NULL | [SELECT marc_bf_subjects(fields) FROM read_marc('records.mrc')] |
| marc_bf_title | macro | Helper for marc_bibframe_jsonld: renders 245 $a and $b as a bf:Title JSON object with mainTitle and subtitle; NULL when both are absent | NULL | [SELECT marc_bf_title(fields) FROM read_marc('records.mrc')] |
| marc_bf_vocab | macro | Helper for marc_bibframe_jsonld: renders codes vals as a JSON array of {@id: base_ || code} references, or NULL for an empty list | NULL | [marc_bf_vocab(['txt'], 'http://id.loc.gov/vocabulary/contentTypes/')] |
| marc_bf_work | macro | Helper for marc_bibframe_jsonld: renders the BIBFRAME Work node with @id uri_ || '#work': type, title, contributions, subjects, language, classification, content and hasInstance | NULL | [SELECT marc_bf_work(leader, fields, marc_bf_id(fields)) FROM read_marc('records.mrc')] |
| marc_bf_worktype | macro | Helper for marc_bibframe_jsonld: returns the BIBFRAME Work subclass for Leader/06 (bf:Text, bf:NotatedMusic, bf:Cartography, bf:Audio, …), or NULL when only bf:Work applies | NULL | [marc_bf_worktype('00714cam a2200205 a 4500')] |
| marc_bib_level | macro | Returns Leader/07, the one-character bibliographic-level code (for example 'm' monograph, 's' serial) | NULL | [marc_bib_level('00714cam a2200205 a 4500')] |
| marc_bibframe_jsonld | macro | Converts a record to BIBFRAME 2 JSON-LD text: an @graph of a Work and an Instance node identified by the 001 plus #work / #instance; keys without source data are omitted | NULL | [SELECT marc_bibframe_jsonld(leader, fields) FROM read_marc('records.mrc')] |
| marc_build_field | scalar | Adds a field built from a one-line breaker template whose {tag$code} placeholders are filled from the record's first matching subfield, inserted in tag order; errors unless the template is exactly one field line | NULL | [SELECT marc_build_field(fields, '=953 10$a{245$a} / {100$a}') FROM read_marc('records.mrc')] |
| marc_change_case | scalar | Recases subfield values in data fields matching the tag pattern to 'upper', 'lower' or 'title'; code '*' targets every subfield, and only ASCII and Latin-1 letters change case | NULL | [SELECT marc_change_case(fields, '245', 'a', 'title') FROM read_marc('records.mrc')] |
| marc_check_urls | table_macro | Builds a link-audit worklist for a MARC file, one row per 856 with a $u, giving control_number, uri, link_text, materials and parsed scheme and host; it does not fetch the links | NULL | [SELECT * FROM marc_check_urls('records.mrc')] |
| marc_clear_linked_uris | scalar | Removes every $0 (code '0') or $1 (code '1') subfield from data fields matching the tag pattern; any other code is an error | NULL | [SELECT marc_clear_linked_uris(fields, '6..', '0') FROM read_marc('records.mrc')] |
| marc_cluster_headings | table_macro | Clusters a file's 1XX/6XX/7XX headings by key collision, one row per key with n_headings, n_variants and sorted members; method is 'fingerprint' or 'ngram' (bigram), anything else errors | NULL | [SELECT * FROM marc_cluster_headings('records.mrc', 'fingerprint') WHERE n_variants > 1] |
| marc_control_field | macro | Returns the value of the first field tagged tag_ (meant for control fields such as 001 or 008), or NULL when absent | NULL | [SELECT marc_control_field(fields, '008') FROM read_marc('records.mrc')] |
| marc_control_pos | macro | Returns len characters of a control-field value starting at 0-based position start_pos, matching MARC numbering such as 008/35-37 | NULL | [marc_control_pos('850101s1985 nyua 000 1 eng d', 35, 3)] |
| marc_copy_field | scalar | Copies every field matching the tag pattern under a literal new tag, keeping the originals and inserting the copies in tag order; copying across the control/data boundary is an error | NULL | [SELECT marc_copy_field(fields, '245', '246') FROM read_marc('records.mrc')] |
| marc_cutter_valid | scalar | Returns true when the value has Cutter-number shape: an optional leading dot, one or two ASCII letters, then digits and nothing after | NULL | [marc_cutter_valid('.C45')] |
| marc_ddc_sortkey | scalar | Returns a shelf-order collation key for a Dewey call number, stripping letter prefixes and uppercasing the item part; NULL when the value is not a Dewey number | NULL | [marc_ddc_sortkey('813.54 F553w')] |
| marc_dedupe_candidates | table_macro | Lists likely duplicate record pairs in a file or glob, one row per pair whose NACO title Jaro-Winkler similarity is at least threshold or whose match keys are equal | NULL | [SELECT * FROM marc_dedupe_candidates('records.mrc', 0.9)] |
| marc_dedupe_key | macro | Returns the marc_matchkey deduplication key (normalized title, ISBN-13 and year) under its workflow name | NULL | [SELECT marc_dedupe_key(fields) FROM read_marc('records.mrc')] |
| marc_delimited_assemble | macro | Helper for marc_from_delimited: merges built fields into the marc_new_record(material) skeleton in tag order, replacing skeleton fields whose tag was mapped, as STRUCT(leader, fields) | NULL | [marc_delimited_assemble('book', marc_delimited_build(marc_delimited_clean([{tag: '245', i1: '1', i2: '0', code: 'a', value: 'A title'}])))] |
| marc_delimited_build | macro | Helper for marc_from_delimited: turns cleaned mapping entries mm into field structs, merging data entries sharing (tag, i1, i2) into one field and making tags below 010 control fields | NULL | [marc_delimited_build(marc_delimited_clean([{tag: '264', i1: ' ', i2: '1', code: 'b', value: 'Publisher'}, {tag: '264', i1: ' ', i2: '1', code: 'c', value: '1999'}]))] |
| marc_delimited_clean | macro | Helper for marc_from_delimited: keeps the mapping entries that have a tag and a non-blank value; a NULL mapping counts as an empty list | NULL | [marc_delimited_clean([{tag: '245', i1: '1', i2: '0', code: 'a', value: 'A title'}, {tag: '020', i1: ' ', i2: ' ', code: 'a', value: ''}])] |
| marc_delimited_preset_books | macro | Returns the marc_from_delimited mapping list for a book spreadsheet: 100 author, 245 title (ind1 1 when an author is present, else 0), 020 isbn, one 264 _1 with publisher and pubyear | NULL | [marc_delimited_preset_books('A title /', 'Smith, Jane.', '0716717972', 'Example Press', '1999')] |
| marc_delimited_preset_eresources | macro | Returns the marc_from_delimited mapping list for an e-resource spreadsheet: 245 title, 856 40 $u url and 020 isbn; pair with material 'electronic' | NULL | [marc_delimited_preset_eresources('Online thing', 'https://example.org/thing', '0716717972')] |
| marc_delimited_preset_serials | macro | Returns the marc_from_delimited mapping list for a serial spreadsheet: 245 title, 022 issn and 264 _1 $b publisher; pair with material 'serial' | NULL | [marc_delimited_preset_serials('Journal of Tests', '0378-5955', 'Example Press')] |
| marc_delimited_tagsort | macro | Helper for marc_from_delimited: returns the fields list fs sorted by tag, keeping the relative order of fields that share a tag | NULL | [SELECT marc_delimited_tagsort(fields) FROM read_marc('records.mrc')] |
| marc_diff | scalar | Returns the field-level differences between two records as LIST(STRUCT(kind, tag, field_no_a, field_no_b, a, b)), pairing fields by tag occurrence and rendering each side as breaker text | NULL | [SELECT marc_diff(leader, fields, leader, marc_sort_fields(fields)) FROM read_marc('records.mrc')] |
| marc_dublin_core | macro | Crosswalks a record to a simple Dublin Core STRUCT (title, creator, subject, description, publisher, contributor, date, type, format, identifier, language, relation, coverage, rights) following the LC mapping | NULL | [SELECT marc_dublin_core(leader, fields) FROM read_marc('records.mrc')] |
| marc_encoding_scheme | macro | Returns 'utf8' when Leader/09 is 'a' and 'marc8' for any other value | NULL | [marc_encoding_scheme('00714cam a2200205 a 4500')] |
| marc_expand_863 | macro | Renders a caption/value field pair as readable holdings such as 'v.3:no.2(1998:Mar.)', with parenthesized chronology captions grouped in one trailing parenthesis | NULL | [SELECT marc_expand_863(p.caption_field, p.value_field) FROM (SELECT unnest(marc_holdings_pairs(fields)) AS p FROM read_marc('records.mrc'))] |
| marc_fieldbreak | macro | Renders one field struct f as a MARC breaker line such as '=245 10$aTitle', with blank indicators written as backslashes | NULL | [SELECT marc_fieldbreak(fields[1]) FROM read_marc('records.mrc')] |
| marc_fields | macro | Returns the field structs in fields whose tag equals tag_ exactly, as a list in record order (no wildcards) | NULL | [SELECT marc_fields(fields, '650') FROM read_marc('records.mrc')] |
| marc_fingerprint | scalar | Returns the OpenRefine-style fingerprint clustering key of a value: diacritics folded, lowercased, punctuation stripped, tokens sorted and de-duplicated, joined with single spaces | NULL | [marc_fingerprint('Cruise, Tom')] |
| marc_folio_check | macro | Returns FOLIO data-import problems as LIST(VARCHAR): missing 001, 001 without 003, a reserved 999 ff field present, or Leader/09 not 'a' (UTF-8) | NULL | [SELECT marc_folio_check(leader, fields) FROM read_marc('records.mrc')] |
| marc_format | macro | Returns the MARC 21 format from Leader/06: 'authority' (z), 'holdings' (u v x y), 'classification' (w), 'community' (q), otherwise 'bibliographic' | NULL | [marc_format('00714cam a2200205 a 4500')] |
| marc_from_delimited | macro | Builds one STRUCT(leader, fields) record from a spreadsheet row: the marc_new_record(material) skeleton plus mapping entries {tag, i1, i2, code, value}; blank values drop, NULL code means $a | NULL | [marc_from_delimited('book', [{tag: '245', i1: '1', i2: '0', code: 'a', value: 'A title'}, {tag: '100', i1: '1', i2: ' ', code: 'a', value: 'Smith, Jane'}])] |
| marc_from_delimited_url | macro | Builds a record like marc_from_delimited and also appends an 856 40 field from url with optional link_text as $z when url is not blank | NULL | [marc_from_delimited_url('electronic', [{tag: '245', i1: '0', i2: '0', code: 'a', value: 'Online thing'}], 'https://example.org/thing', 'Online access')] |
| marc_from_unimarc_label | macro | Converts a UNIMARC record label to a MARC 21 leader, mapping status, type, bibliographic level, encoding level and cataloguing form and setting Leader/09 to 'a' | NULL | [marc_from_unimarc_label('00714cam0 2200205 450 ')] |
| marc_generate_33x | scalar | Returns the fields with RDA 336/337/338 content, media and carrier fields added where missing, derived from the leader, 008/26, 006 and 007; existing 33X tags are left untouched | NULL | [SELECT marc_generate_33x(leader, fields) FROM read_marc('records.mrc')] |
| marc_heading_flips | macro | Returns the bib_headings that NACO-match a see_from tracing in auth_refs (from marc_authority_refs), each paired with that record's authorized heading as LIST(STRUCT(heading, authorized)) | NULL | [SELECT marc_heading_flips(['Clemens, Samuel'], marc_authority_refs(leader, fields)) FROM read_marc('records.mrc')] |
| marc_heading_join | macro | Returns one heading string for field struct f: its a-z subfield values except $w joined with single spaces, so $0-$9 and $w are excluded | NULL | [SELECT marc_heading_join(marc_fields(fields, '100')[1]) FROM read_marc('records.mrc')] |
| marc_headings | macro | Returns the record's heading strings for 100/110/111/130, every 6XX and 700/710/711/730/740, each via marc_heading_join, as LIST(VARCHAR) | NULL | [SELECT marc_headings(fields) FROM read_marc('records.mrc')] |
| marc_headings_naco | macro | Returns marc_headings(fields) with each heading converted to its NACO comparison form, for matching across records | NULL | [SELECT marc_headings_naco(fields) FROM read_marc('records.mrc')] |
| marc_holdings_pairs | macro | Pairs each 863/864/865 with its 853/854/855 caption on the $8 link number, as LIST(STRUCT(link, caption_field, value_field)); caption_field is NULL when unmatched | NULL | [SELECT marc_holdings_pairs(fields) FROM read_marc('records.mrc')] |
| marc_idloc_candidates | macro | Shapes one id.loc.gov suggest2 JSON response j (the STRUCT read_json produces) into LIST(STRUCT(label, uri, token)) candidates from its hits array | NULL | [SELECT unnest(marc_idloc_candidates(j)) FROM read_json('https://id.loc.gov/authorities/names/suggest2?q=Twain&count=5', records := false) t(j)] |
| marc_idloc_label_url | macro | Builds an id.loc.gov known-label lookup URL for an exact authorized label without fetching; scheme defaults to 'names' (also 'subjects', 'genreForms', …) | NULL | [marc_idloc_label_url('Twain, Mark, 1835-1910', scheme := 'names')] |
| marc_idloc_suggest_url | macro | Builds an id.loc.gov suggest2 keyword-search URL for term without fetching; scheme defaults to 'names' and count_ to 5 | NULL | [marc_idloc_suggest_url('Twain, Mark', count_ := 10)] |
| marc_ids_fast | macro | Returns the record's $0/$1 values that are OCLC FAST identifiers ('(OCoLC)fst' prefixes or worldcat.org/fast and fast.oclc.org URIs), as LIST(VARCHAR) | NULL | [SELECT marc_ids_fast(fields) FROM read_marc('records.mrc')] |
| marc_ids_lc | macro | Returns the record's $0/$1 values containing id.loc.gov, as LIST(VARCHAR) | NULL | [SELECT marc_ids_lc(fields) FROM read_marc('records.mrc')] |
| marc_ids_viaf | macro | Returns the record's $0/$1 values containing viaf.org, as LIST(VARCHAR) | NULL | [SELECT marc_ids_viaf(fields) FROM read_marc('records.mrc')] |
| marc_instance | macro | Maps a bibliographic record to a FOLIO-instance-shaped STRUCT: title, contributors, identifiers (lccn, isbn, issn, system control number), publication, languages, subjects, edition, physical_description | NULL | [SELECT marc_instance(leader, fields) FROM read_marc('records.mrc')] |
| marc_isbd | macro | Renders a bibliographic record as one ISBD display string, joining areas 1, 2, 4, 5, 6 and 8 with '. — '; absent areas drop and an empty record yields NULL | NULL | [SELECT marc_isbd(leader, fields) FROM read_marc('records.mrc')] |
| marc_isbd_area1 | macro | Helper for marc_isbd: renders ISBD area 1 from 245 as 'title : other title / responsibility', with source punctuation trimmed; NULL when empty | NULL | [SELECT marc_isbd_area1(fields) FROM read_marc('records.mrc')] |
| marc_isbd_area2 | macro | Helper for marc_isbd: renders ISBD area 2 (edition) from 250 $a and $b as 'edition / responsibility'; NULL when empty | NULL | [SELECT marc_isbd_area2(fields) FROM read_marc('records.mrc')] |
| marc_isbd_area4 | macro | Helper for marc_isbd: renders ISBD area 4 as 'place : publisher, date' from 260, falling back to 264; NULL when empty | NULL | [SELECT marc_isbd_area4(fields) FROM read_marc('records.mrc')] |
| marc_isbd_area5 | macro | Helper for marc_isbd: renders ISBD area 5 from 300 as 'extent : other physical details ; dimensions'; NULL when empty | NULL | [SELECT marc_isbd_area5(fields) FROM read_marc('records.mrc')] |
| marc_isbd_area6 | macro | Helper for marc_isbd: renders ISBD area 6 as '(series ; numbering)' from 490 $a and $v; NULL when there is no 490 $a | NULL | [SELECT marc_isbd_area6(fields) FROM read_marc('records.mrc')] |
| marc_isbd_area8 | macro | Helper for marc_isbd: renders ISBD area 8 as 'ISSN …' for continuing resources (Leader/07 b, i, s) with a 022, else 'ISBN …' from 020 $a, as transcribed | NULL | [SELECT marc_isbd_area8(leader, fields) FROM read_marc('records.mrc')] |
| marc_isbd_nn | macro | Helper for marc_isbd: returns NULL for an empty string and the value otherwise, so empty concat_ws segments drop out | NULL | [marc_isbd_nn('')] |
| marc_isbn13 | scalar | Normalizes an ISBN-10 or ISBN-13 to its hyphen-free 13-digit form, ignoring labels and trailing qualifiers; NULL when the value is not a valid ISBN | NULL | [marc_isbn13('0-306-40615-2')] |
| marc_issn | scalar | Normalizes an ISSN to the hyphenated 9999-999X form after validating its check digit; NULL when the value is not a valid ISSN | NULL | [marc_issn('0378-5955')] |
| marc_jsonld | macro | Returns a schema.org JSON-LD object text for a record: @type from the leader, name, author, publisher, datePublished, about, normalized isbn/issn, url and sameAs URIs; absent data omits keys | NULL | [SELECT marc_jsonld(leader, fields) FROM read_marc('records.mrc')] |
| marc_jsonld_arr | macro | Helper for marc_jsonld: renders a list of values as a JSON array text of escaped string literals | NULL | [marc_jsonld_arr(['Rivers', 'Poland'])] |
| marc_jsonld_body | macro | Helper for marc_jsonld: returns the list of JSON key:value member fragments built from the record and its marc_dublin_core struct dc, with NULL for keys lacking data | NULL | [SELECT marc_jsonld_body(leader, fields, marc_dublin_core(leader, fields)) FROM read_marc('records.mrc')] |
| marc_jsonld_str | macro | Helper for marc_jsonld: renders a value as a quoted JSON string literal, escaping backslash, double quote, newline, carriage return and tab | NULL | [marc_jsonld_str('Rivers of Poland')] |
| marc_jsonld_type | macro | Helper for marc_jsonld: returns the schema.org @type for a leader from Leader/06-07, such as Book, Periodical, Map, MusicComposition, Movie, else CreativeWork | NULL | [marc_jsonld_type('00714cam a2200205 a 4500')] |
| marc_kbart_856 | macro | Builds an 856 40 field struct with $u title_url and, when link_text is not NULL, a $z public note; ready for marc_add_field | NULL | [marc_kbart_856('https://example.org/title/123', 'Online access')] |
| marc_kbart_to_marc | macro | Builds a brief e-resource STRUCT(leader, fields) from KBART values: 022 or 020 from online_identifier by validity, 245 00 title, 260 $b publisher, 856 40 URL; NULL inputs drop fields | NULL | [marc_kbart_to_marc('Journal of Examples', '0378-5955', 'https://example.org/joe', 'Example Press')] |
| marc_lcc_parse | scalar | Parses an LC call number into STRUCT(class, number, decimal, cutters, year, rest, valid); valid is false and the parts empty when the class letters or number are malformed | NULL | [marc_lcc_parse('QA76.73.J38 S25 2004')] |
| marc_lcc_sortkey | scalar | Returns a collation key whose plain ORDER BY gives correct LC shelf order for a Library of Congress call number; NULL when the call number is invalid | NULL | [marc_lcc_sortkey('QA76.73.J38 S25 2004')] |
| marc_lccn | scalar | Normalizes an LCCN per LC's published rules (blanks removed, serial zero-padded to six digits, lowercased); NULL when the result is empty or not alphanumeric | NULL | [marc_lccn('n 78-890351')] |
| marc_leader_pos | macro | Returns the single leader character at 0-based position pos, matching MARC documentation numbering such as Leader/06 | NULL | [marc_leader_pos('00714cam a2200205 a 4500', 6)] |
| marc_leader_struct | macro | Returns the leader's fixed positions as a STRUCT: record_length, status, type, bib_level, control_type, encoding, counts, base_address, encoding_level, cataloging_form, multipart_level | NULL | [marc_leader_struct('00714cam a2200205 a 4500')] |
| marc_matchkey | scalar | Returns a deduplication key joining the NACO-normalized 245 $a$b, the first valid 020 ISBN-13 and the first 260/264 $c year with '|'; NULL when all three are missing | NULL | [SELECT marc_matchkey(fields) FROM read_marc('records.mrc')] |
| marc_merge | scalar | Merges incoming fields into base under comma-separated tag-pattern lists that protect, replace or add tags, with 'keep', 'replace' or 'add' as the default action for other tags | NULL | [SELECT marc_merge(a.fields, b.fields, '001,003', '6..', '', 'keep') FROM read_marc('records.mrc') a JOIN read_marc('records.mrc') b ON a.record_no = b.record_no] |
| marc_mods_origininfo | macro | Helper for marc_mods_xml: renders a publication STRUCT(place, publisher, date) as a MODS originInfo XML element, or an empty string when all three are NULL | NULL | [SELECT marc_mods_origininfo((marc_instance(leader, fields)).publication) FROM read_marc('records.mrc')] |
| marc_mods_xml | macro | Renders a record as a minimal well-formed MODS XML string (titleInfo, name, typeOfResource, originInfo, language, physicalDescription, subject, ISBN/ISSN identifiers), emitting only elements with source data | NULL | [SELECT marc_mods_xml(leader, fields) FROM read_marc('records.mrc')] |
| marc_move_field | scalar | Renumbers every field matching the tag pattern to a literal new tag in place, keeping order and indicators; moving across the control/data field boundary is an error | NULL | [SELECT marc_move_field(fields, '440', '490') FROM read_marc('records.mrc')] |
| marc_naco | scalar | Returns the NACO authority comparison form of a heading (uppercased, diacritics folded, punctuation normalized) for matching; NULL when nothing remains | NULL | [marc_naco('Twain, Mark, 1835-1910.')] |
| marc_nearest_dupes | table_macro | Lists, for every record of a file, its five most title-similar other records via an exact NEAREST join, one directed row per pair; min_similarity defaults to 0.0, equal match keys always kept | NULL | [SELECT * FROM marc_nearest_dupes('records.mrc', min_similarity := 0.85)] |
| marc_nearest_headings | table_macro | Matches each distinct heading of a MARC file to its three most similar candidate labels (a VARCHAR list) by NACO-normalized Jaro-Winkler, one row per pair; min_similarity defaults to 0.0 | NULL | [SELECT * FROM marc_nearest_headings('records.mrc', ['Twain, Mark, 1835-1910', 'Rivers'])] |
| marc_nearest_key | macro | Returns the title ranking key for nearest-neighbour matching: 245 $a and $b joined and NACO-normalized, without truncation; NULL when the record has no 245 $a | NULL | [SELECT marc_nearest_key(fields) FROM read_marc('records.mrc')] |
| marc_nearest_matches | table_macro | Lists, for every record of query_path, the five most title-similar records of target_path, one row per pair with similarity and same_matchkey; min_similarity defaults to 0.0 | NULL | [SELECT * FROM marc_nearest_matches('records.mrc', 'records.mrc', min_similarity := 0.9)] |
| marc_nearest_matches_k | table_macro | Lists, for every record of query_path, the k most title-similar records of target_path (marc_nearest_matches columns, no threshold), with k any positive integer | NULL | [SELECT * FROM marc_nearest_matches_k('records.mrc', 'records.mrc', 10)] |
| marc_nearest_matches_sql | macro | Builds, without running it, the SQL text of a marc_nearest_matches-style top-k join of query_path against target_path with k neighbours; k must be a positive integer | NULL | [marc_nearest_matches_sql('records.mrc', 'records.mrc', 10)] |
| marc_nearest_recs | table_macro | Reads a MARC file or glob as one row per record with file, record_no, title (245 $a), tkey (marc_nearest_key) and matchkey, the input for NEAREST joins | NULL | [SELECT * FROM marc_nearest_recs('records.mrc')] |
| marc_nearest_sql_quote | macro | Helper for marc_nearest_matches_sql: wraps a string in single quotes with embedded quotes doubled, as a SQL string literal | NULL | [marc_nearest_sql_quote('records.mrc')] |
| marc_new_record | scalar | Returns a skeleton STRUCT(leader, fields) for 'book', 'serial', 'video', 'map', 'music', 'electronic' or 'authority', with leader, 008 and an empty 245 (100 for authority); unknown materials error | NULL | [marc_new_record('book')] |
| marc_ngram_fingerprint | scalar | Returns the OpenRefine-style n-gram clustering key of a value: sorted unique n-grams of the folded, space-less string; n must be at least 1 | NULL | [marc_ngram_fingerprint('banana', 2)] |
| marc_oai_extract_token | macro | Extracts the trimmed resumptionToken text from one OAI-PMH response document xml; NULL when the element is absent or empty, which marks the end of the list | NULL | [marc_oai_extract_token(' |
| marc_oai_page_url | macro | Builds the OAI-PMH ListRecords follow-up URL for a resumption_token (URL-encoded, with no other arguments as the protocol requires) without fetching | NULL | [marc_oai_page_url('https://example.org/oai', 'token123')] |
| marc_oai_token | table_macro | Reads one OAI-PMH response via read_text and returns a single row with token, complete_list_size, cursor and expiration_date; a remote url needs LOAD httpfs | NULL | [SELECT * FROM marc_oai_token('https://example.org/oai?verb=ListRecords&metadataPrefix=marc21')] |
| marc_oai_url | macro | Builds the first-page OAI-PMH ListRecords URL for base without fetching; metadata_prefix defaults to 'marc21', with optional set_, from_ and until_ harvest window | NULL | [marc_oai_url('https://example.org/oai', set_ := 'books', from_ := '2024-01-01')] |
| marc_oclc | scalar | Normalizes an OCLC number to bare digits, stripping an (OCoLC) label, ocm/ocn/on prefix and leading zeros; NULL when what remains is not all digits | NULL | [marc_oclc('(OCoLC)ocm00012345')] |
| marc_parse_json | scalar | Parses MARC-in-JSON text, including FOLIO SRS parsedRecord/content envelopes, into STRUCT(leader, fields) for the first record it contains; NULL when there is none | NULL | [SELECT marc_parse_json(content) FROM read_text('records.json')] |
| marc_rank | scalar | Scores a record for copy-cataloging selection as STRUCT(encoding_level_rank, completeness, total), each 0-100, from Leader/17 and weighted field presence; higher total means a fuller record | NULL | [SELECT marc_rank(leader, fields) FROM read_marc('records.mrc')] |
| marc_rda_check | macro | Returns AACR2-era issue codes for a bibliographic record as LIST(VARCHAR), such as desc_aacr2, missing_336, gmd_245h, only_260, aacr2_abbreviations, no_relator_1xx; empty for non-bibliographic leaders | NULL | [SELECT marc_rda_check(leader, fields) FROM read_marc('records.mrc')] |
| marc_rda_expand | scalar | Expands classic AACR2 abbreviations (p., v., ill., col., port., facsim., ed.) to RDA spelled-out forms in 250 $a and 300 subfields only, leaving other fields untouched | NULL | [SELECT marc_rda_expand(fields) FROM read_marc('records.mrc')] |
| marc_read_kbart | table_macro | Reads a tab-separated KBART title list with its header row, one row per title, every column VARCHAR under its original header name | NULL | [SELECT * FROM marc_read_kbart('kbart.txt')] |
| marc_readalma | table_macro | Fetches one Alma bib by mms_id from the Bibs API at base and reads it as one row per subfield; apikey is optional (else send it in an HTTP secret); needs LOAD httpfs | NULL | [SELECT * FROM marc_readalma('https://api-na.hosted.exlibrisgroup.com', '991234567890123')] |
| marc_readalma_bibs | table_macro | Fetches up to 100 Alma bibs for a comma-separated mms_ids list from the Bibs API and reads them as one row per subfield; optional apikey; needs LOAD httpfs | NULL | [SELECT * FROM marc_readalma_bibs('https://api-na.hosted.exlibrisgroup.com', '991234567890123,991234567890124')] |
| marc_readalma_sru | table_macro | Runs an SRU search against an Alma institution's endpoint (base/view/sru/inst_code) for query_ via marc_readsru, one row per subfield; max_records defaults to 50; needs LOAD httpfs | NULL | [SELECT * FROM marc_readalma_sru('https://example.alma.exlibrisgroup.com', '01EXAMPLE_INST', 'alma.title=rivers')] |
| marc_readfields | table_macro | Reads an ISO 2709 file as one row per field, with data-field subfields as a LIST(STRUCT(code, value)) and control-field text in control_value | NULL | [SELECT * FROM marc_readfields('records.mrc')] |
| marc_readfieldsbreaker | table_macro | Reads a MARC breaker (.mrk) file as one row per field (the marc_readfields shape over read_marc_breaker), with subfields as a list and control text in control_value | NULL | [SELECT * FROM marc_readfieldsbreaker('records.mrk')] |
| marc_readfieldsxml | table_macro | Reads a MARCXML file as one row per field (the marc_readfields shape over read_marcxml), with subfields as a list and control-field text in control_value | NULL | [SELECT * FROM marc_readfieldsxml('records.xml')] |
| marc_readfolio_record | table_macro | Fetches one FOLIO SRS record by its record_uuid (the 999 ff $s value) from okapi_base and reads it as one row per subfield; needs LOAD httpfs and tenant/token headers | NULL | [SELECT * FROM marc_readfolio_record('https://okapi.example.org', '00000000-0000-0000-0000-000000000002')] |
| marc_readfolio_source_records | table_macro | Fetches one page of FOLIO SRS source-records (limit lim, offset off) from okapi_base and reads it as one row per subfield; needs LOAD httpfs and tenant/token headers | NULL | [SELECT * FROM marc_readfolio_source_records('https://okapi.example.org', 100, 0)] |
| marc_readfolio_srs | table_macro | Fetches the FOLIO SRS source record for one instance_uuid through the gateway at okapi_base and reads it as one row per subfield; needs LOAD httpfs and tenant/token headers in an HTTP secret | NULL | [SELECT * FROM marc_readfolio_srs('https://okapi.example.org', '00000000-0000-0000-0000-000000000001')] |
| marc_readkoha | table_macro | Fetches one Koha biblio as MARCXML from the staff REST API at base and reads it as one row per subfield; needs LOAD httpfs and an HTTP secret with Accept and credentials | NULL | [SELECT * FROM marc_readkoha('https://staff.mylib.example', 42)] |
| marc_readkoha_opac | table_macro | Fetches one Koha biblio via the OPAC opac-export.pl MARCXML export (no auth or headers) and reads it as one row per subfield; needs LOAD httpfs | NULL | [SELECT * FROM marc_readkoha_opac('https://opac.mylib.example', 42)] |
| marc_readkoha_public | table_macro | Fetches one Koha biblio from the public REST API at base and reads it as one row per subfield; needs LOAD httpfs and an HTTP secret sending the MARCXML Accept header | NULL | [SELECT * FROM marc_readkoha_public('https://opac.mylib.example', 42)] |
| marc_readnestedbreaker | table_macro | Reads a MARC breaker (.mrk) file as one row per record with leader and a nested fields list, the read_marc shape over read_marc_breaker | NULL | [SELECT * FROM marc_readnestedbreaker('records.mrk')] |
| marc_readnestedxml | table_macro | Reads a MARCXML file as one row per record with leader and a nested fields list, the read_marc shape over read_marcxml | NULL | [SELECT * FROM marc_readnestedxml('records.xml')] |
| marc_readoai | table_macro | Fetches the first OAI-PMH ListRecords page from base and reads it as one row per subfield; metadata_prefix defaults to 'marc21', optional set_; needs LOAD httpfs | NULL | [SELECT * FROM marc_readoai('https://example.org/oai', set_ := 'books')] |
| marc_readoai_page | table_macro | Fetches one OAI-PMH ListRecords follow-up page for resumption_token from base and reads it as one row per subfield, like marc_readoai; needs LOAD httpfs | NULL | [SELECT * FROM marc_readoai_page('https://example.org/oai', 'token123')] |
| marc_readsru | table_macro | Runs an SRU searchRetrieve against base for CQL query_ and reads the MARCXML response as one row per subfield; max_records defaults to 50, one page only; needs LOAD httpfs | NULL | [SELECT * FROM marc_readsru('https://example.org/sru', 'dc.title=rivers', max_records := 10)] |
| marc_readworldshare | table_macro | Fetches one WorldCat bib by oclc_number from the OCLC Metadata API at base and reads it as one row per subfield; needs LOAD httpfs plus an HTTP secret with bearer token and Accept header | NULL | [SELECT * FROM marc_readworldshare('https://metadata.api.oclc.org', '12345')] |
| marc_reconcile_headings | table_macro | Builds the reconciliation queue for a MARC file, one row per distinct heading with n_occurrences and a lookup url; base is 'idloc', 'viaf', 'wikidata' or a literal URL prefix; nothing is fetched | NULL | [SELECT * FROM marc_reconcile_headings('records.mrc', 'viaf')] |
| marc_record_type | macro | Returns Leader/06, the one-character type-of-record code (for example 'a' language material, 'z' authority) | NULL | [marc_record_type('00714cam a2200205 a 4500')] |
| marc_recordbreak | macro | Renders a whole record as MARC breaker text: an =LDR line followed by one marc_fieldbreak line per field, newline-separated | NULL | [SELECT marc_recordbreak(leader, fields) FROM read_marc('records.mrc')] |
| marc_remove_fields | scalar | Deletes every field whose tag matches the 3-character tag pattern, where '.' is a single-position wildcard | NULL | [SELECT marc_remove_fields(fields, '9..') FROM read_marc('records.mrc')] |
| marc_remove_fields_where | scalar | Deletes data fields matching the tag pattern that have a subfield with the given code whose value matches the ECMAScript regex (searched anywhere, case-sensitive) | NULL | [SELECT marc_remove_fields_where(fields, '6..', 'a', '^Rivers') FROM read_marc('records.mrc')] |
| marc_remove_subfield | scalar | Deletes every subfield with the given code from data fields matching the tag pattern, dropping a field entirely when no subfields remain | NULL | [SELECT marc_remove_subfield(fields, '245', 'h') FROM read_marc('records.mrc')] |
| marc_rename_subfield | scalar | Changes subfield code from_code to to_code in every data field matching the tag pattern, keeping values and subfield order | NULL | [SELECT marc_rename_subfield(fields, '650', 'z', 'x') FROM read_marc('records.mrc')] |
| marc_replace_all | scalar | Applies a list of regex replacement rules STRUCT(tag, code, find, replace) in order, each over matching subfield values; a NULL code covers every subfield and errors name the failing rule | NULL | [SELECT marc_replace_all(fields, [{tag: '650', code: 'a', find: '^Rivers$', replace: 'Streams'}]) FROM read_marc('records.mrc')] |
| marc_replace_values | scalar | Replaces every ECMAScript regex match with the replacement text in values of the given subfield code, across data fields matching the tag pattern | NULL | [SELECT marc_replace_values(fields, '650', 'a', '.$', '') FROM read_marc('records.mrc')] |
| marc_report_completeness | table_macro | Reports per-record completeness for a MARC file, one row per record with has_title, has_author, has_subject, has_isbn, has_language, has_publication, has_physical flags and a 0-100 score | NULL | [SELECT * FROM marc_report_completeness('records.mrc')] |
| marc_report_errors | table_macro | Reports marc_validate structural violations for a MARC file, one row per violation with file, record_no and control_number; passing records produce no rows | NULL | [SELECT * FROM marc_report_errors('records.mrc')] |
| marc_report_subfields | table_macro | Reports subfield-code frequency within tag tag_ of a MARC file, one row per code with count and records_with | NULL | [SELECT * FROM marc_report_subfields('records.mrc', '245')] |
| marc_report_tags | table_macro | Reports tag frequency for a MARC file, one row per tag with total count, records_with and pct_records (share of records containing it) | NULL | [SELECT * FROM marc_report_tags('records.mrc')] |
| marc_set_indicators | scalar | Sets both indicators on every data field matching the tag pattern; each indicator must be exactly one character and control fields are skipped | NULL | [SELECT marc_set_indicators(fields, '245', '1', '0') FROM read_marc('records.mrc')] |
| marc_set_linked_uri | scalar | Sets $0 (code '0') or $1 (code '1') to the URI on fields matching the tag pattern whose joined heading NACO-matches the given heading, replacing an existing link or appending one | NULL | [SELECT marc_set_linked_uri(fields, '650', 'Rivers', 'http://id.loc.gov/authorities/subjects/sh85114361', '0') FROM read_marc('records.mrc')] |
| marc_set_subfield | scalar | Sets the value of the given subfield on every data field matching the tag pattern, appending the subfield at the end where it is missing | NULL | [SELECT marc_set_subfield(fields, '040', 'a', 'XX-XxUND') FROM read_marc('records.mrc')] |
| marc_sf01 | macro | Helper for marc_ids_lc, marc_ids_viaf and marc_ids_fast: returns every $0 and $1 value in the record, URI-shaped or not, as LIST(VARCHAR) | NULL | [SELECT marc_sf01(fields) FROM read_marc('records.mrc')] |
| marc_sort_fields | scalar | Returns the fields stably sorted by tag, so fields sharing a tag keep their original occurrence order | NULL | [SELECT marc_sort_fields(fields) FROM read_marc('records.mrc')] |
| marc_spec | scalar | Evaluates a MARCspec subset (245$a$b, 6.., LDR/6, 008/35-37, [n] and [#] indexes, ^1 indicators) against a record and returns the matched values as LIST(VARCHAR); invalid specs error | NULL | [SELECT marc_spec(leader, fields, '245$a') FROM read_marc('records.mrc')] |
| marc_stamp | macro | Adds a 949 load-stamp field with $a org_code and $d today's date as YYYY-MM-DD, inserted in tag order | NULL | [SELECT marc_stamp(fields, 'XXX') FROM read_marc('records.mrc')] |
| marc_subfield | macro | Returns the first value of subfield code_ in the data fields tagged tag_, or NULL when the record has none | NULL | [SELECT marc_subfield(fields, '245', 'a') FROM read_marc('records.mrc')] |
| marc_subfields | macro | Returns every value of subfield code_ across all data fields tagged tag_, as LIST(VARCHAR) in record order; an empty list when none | NULL | [SELECT marc_subfields(fields, '650', 'a') FROM read_marc('records.mrc')] |
| marc_summary | table_macro | Summarizes a MARC file as a single row: record, file, field and subfield totals, min/max/mean/stddev of fields and subfields per record, mean distinct tags, and MARC-8 record count | NULL | [SELECT * FROM marc_summary('records.mrc')] |
| marc_summary_fields | table_macro | Summarizes tag usage in a MARC file, one row per tag with total occurrences, records_with, pct_records, per-record min/max/mean/stddev and mean subfields per field | NULL | [SELECT * FROM marc_summary_fields('records.mrc')] |
| marc_summary_subfields | table_macro | Summarizes the subfields of tag tag_ in a MARC file, one row per code with occurrences, records_with and min/max/mean/stddev value length | NULL | [SELECT * FROM marc_summary_subfields('records.mrc', '650')] |
| marc_swap_fields | scalar | Exchanges two literal tags in place so every tag_a field becomes tag_b and vice versa, keeping order and indicators; both tags must be on the same control/data side | NULL | [SELECT marc_swap_fields(fields, '100', '700') FROM read_marc('records.mrc')] |
| marc_trim_punct | macro | Strips trailing ISBD punctuation and spaces (any of ' /:;=,.') from a string, so a final period is always removed | NULL | [marc_trim_punct('Rivers of Poland /')] |
| marc_type_of_resource | macro | Returns the MODS typeOfResource term for Leader/06 (text, cartographic, notated music, still image, moving image, …); NULL for codes it does not map | NULL | [marc_type_of_resource('00714cam a2200205 a 4500')] |
| marc_unimarc_label | macro | Converts a MARC 21 leader to a 24-character UNIMARC record label, mapping status, type, bibliographic level, encoding level and cataloguing form; length and base address copied verbatim | NULL | [marc_unimarc_label('00714cam a2200205 a 4500')] |
| marc_uris | macro | Returns every $0/$1 value that starts with 'http' as LIST(STRUCT(tag, code, uri)), in record order | NULL | [SELECT marc_uris(fields) FROM read_marc('records.mrc')] |
| marc_urls_856 | macro | Returns one STRUCT(uri, link_text, materials) per 856 that has a $u: first $u, $y or else $z as link text, and $3 | NULL | [SELECT marc_urls_856(fields) FROM read_marc('records.mrc')] |
| marc_validate | macro | Returns structural violations of a record as LIST(VARCHAR): leader length, Leader/09, tag length, repeated 001, missing 245 (bibliographic only), empty data fields; empty when it passes | NULL | [SELECT marc_validate(leader, fields) FROM read_marc('records.mrc')] |
| marc_validate_auth | macro | Validates a record against the embedded MARC 21 authority Avram rulepack (repeatability, required 008, indicator and subfield codes), returning violation messages as LIST(VARCHAR) | NULL | [SELECT marc_validate_auth(leader, fields) FROM read_marc('records.mrc')] |
| marc_validate_avram | scalar | Validates a record against an Avram JSON schema (required, repeatable, indicator codes, subfields) and returns violation messages as LIST(VARCHAR), empty when it passes | NULL | [SELECT marc_validate_avram(leader, fields, json_object('fields', json_object('245', json_object('required', true)))::VARCHAR) FROM read_marc('records.mrc')] |
| marc_validate_bib | macro | Validates a record against the embedded MARC 21 bibliographic Avram rulepack (repeatability, required 008/245, indicator and subfield codes), returning violation messages as LIST(VARCHAR) | NULL | [SELECT marc_validate_bib(leader, fields) FROM read_marc('records.mrc')] |
| marc_validate_format | macro | Validates a record with the rulepack for its own format from Leader/06 (bibliographic, authority or holdings), falling back to marc_validate for other formats | NULL | [SELECT marc_validate_format(leader, fields) FROM read_marc('records.mrc')] |
| marc_validate_holdings | macro | Validates a record against the embedded MARC 21 holdings Avram rulepack (852, 853-855/863-868, required 008), returning violation messages as LIST(VARCHAR) | NULL | [SELECT marc_validate_holdings(leader, fields) FROM read_marc('records.mrc')] |
| marc_variant | scalar | Converts a record to a VARIANT object with leader and an ordered fields array, where control fields carry only tag and value and data fields carry indicators and subfields | NULL | [SELECT marc_variant(leader, fields) FROM read_marc('records.mrc')] |
| marc_variant_record | scalar | Converts a STRUCT(leader, fields) record, as returned by marc_parse_json or marc_new_record, to the same VARIANT shape marc_variant produces | NULL | [marc_variant_record(marc_new_record('book'))] |
| marc_viaf_candidates | macro | Shapes one VIAF SRU JSON response j (as read_json produces it, records in list form) into LIST(STRUCT(viaf_id, name_type, heading)) candidates | NULL | [SELECT unnest(marc_viaf_candidates(j)) FROM read_json('https://viaf.org/viaf/search?query=local.mainHeadingEl+all+Twain&httpAccept=application/json', records := false) t(j)] |
| marc_viaf_search_url | macro | Builds a VIAF SRU search URL with a JSON response for term without fetching; index_ defaults to 'local.mainHeadingEl' and max_records to 5 | NULL | [marc_viaf_search_url('Twain, Mark')] |
| marc_wikidata_candidates | macro | Shapes one Wikidata wbsearchentities JSON response j (the STRUCT read_json produces) into LIST(STRUCT(id, label, description, concepturi)) candidates | NULL | [SELECT unnest(marc_wikidata_candidates(j)) FROM read_json('https://www.wikidata.org/w/api.php?action=wbsearchentities&format=json&language=en&search=Mark+Twain', records := false) t(j)] |
| marc_wikidata_search_url | macro | Builds a Wikidata wbsearchentities URL for term without fetching; language defaults to 'en' and limit_ to 5 | NULL | [marc_wikidata_search_url('Mark Twain', language := 'en')] |
| marc_xml_escape | macro | Escapes &, <, > and the double-quote character in a string for use in XML element content or attribute values | NULL | [marc_xml_escape('Tom & Jerry <1940>')] |
| marc_xslt_command | macro | Builds the xsltproc command line that runs the registered crosswalk on input_path writing output_path, all shell-quoted; NULL for an unknown crosswalk, and nothing is executed | NULL | [marc_xslt_command('MARC=>MODS', 'records.xml', 'records.mods.xml')] |
| marc_xslt_functions | table | Lists the embedded XSLT crosswalk registry, one row per shipped stylesheet with alias, path, source_format, target_format and description | NULL | [SELECT * FROM marc_xslt_functions()] |
| marc_xslt_library | scalar | Returns the text of the shared lib/marc-utils.xsl stylesheet library that every shipped crosswalk includes | NULL | [marc_xslt_library()] |
| marc_xslt_sheet_file | macro | Returns the bare filename a registered XSLT sheet should be exported as, looked up by alias (case-insensitive) or registry path; NULL when not registered | NULL | [marc_xslt_sheet_file('MARC=>MODS')] |
| marc_xslt_shell_arg | macro | Helper for marc_xslt_command: single-quotes a string as one POSIX shell argument, escaping embedded single quotes | NULL | [marc_xslt_shell_arg('my records.xml')] |
| marc_xslt_stylesheet | scalar | Returns the embedded XSLT stylesheet text for a registry alias (case-insensitive) or exact registry path; NULL when the key is not registered | NULL | [marc_xslt_stylesheet('MARC=>MODS')] |
| read_alephseq | table | Reads an Ex Libris Aleph sequential file (glob and gzip accepted) as one row per subfield, control fields as one row each; named parameter tags filters to a comma-separated tag list | NULL | [SELECT * FROM read_alephseq('records.seq')] |
| read_marc | table | Reads ISO 2709 MARC files (glob and gzip accepted) as one row per record with leader and nested fields; named parameters encoding ('auto', 'utf8', 'marc8'), tags and ignore_errors | NULL | [SELECT * FROM read_marc('records.mrc')] |
| read_marc_breaker | table | Reads MARC breaker (.mrk) mnemonic text files as one row per subfield, control fields as one row each; named parameter tags filters to a comma-separated tag list | NULL | [SELECT * FROM read_marc_breaker('records.mrk')] |
| read_marc_raw | table | Reads ISO 2709 files as one row per record with the original bytes as a BLOB and an error column instead of failing on bad records; named parameter encoding | NULL | [SELECT record_no, error FROM read_marc_raw('records.mrc')] |
| read_marc_subfields | table | Reads ISO 2709 MARC files as one row per subfield (control fields one row each) with record, field and subfield positions; named parameters encoding, tags and ignore_errors | NULL | [SELECT * FROM read_marc_subfields('records.mrc')] |
| read_marcjson | table | Reads MARC-in-JSON files (single object, array, NDJSON or FOLIO SRS envelopes) as one row per subfield, control fields one row each; named parameter tags filters tags | NULL | [SELECT * FROM read_marcjson('records.json')] |
| read_marcxml | table | Reads MARCXML slim files, including SRU and OAI-PMH envelopes, as one row per subfield, control fields one row each; named parameter tags filters to a comma-separated tag list | NULL | [SELECT * FROM read_marcxml('records.xml')] |
| read_microlif | table | Reads MicroLIF vendor files, with or without header lines, as one row per subfield, control fields one row each; named parameter tags filters to a comma-separated tag list | NULL | [SELECT * FROM read_microlif('records.lif')] |
| read_z3950 | table | Searches a live Z39.50 server and returns one row per retrieved record with leader, nested fields and total hits; named parameters max_records (default 10), timeout seconds (default 15), ignore_errors | NULL | [SELECT hits, leader FROM read_z3950('z3950.loc.gov', 7090, 'VOYAGER', '@attr 1=7 0316769487')] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |