A duckdb extension which combines data quality and data preparation tools for tabular data.
Maintainer(s):
jrosskopf
Installing and Loading
INSTALL anofox_tabular FROM community;
LOAD anofox_tabular;
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| anofox_ner_status | table | Returns the status of the NER (Named Entity Recognition) model used for name detection. | NULL | [SELECT * FROM anofox_ner_status();] |
| anofox_tab_currency_name | scalar | Returns the full English name of a currency given its ISO 4217 code (e.g., 'US Dollar'). | NULL | [SELECT currency_name('EUR');] |
| anofox_tab_currency_symbol | scalar | Returns the currency symbol for a given ISO 4217 currency code (e.g., '$' for USD). | NULL | [SELECT currency_symbol('EUR');] |
| anofox_tab_dbscan | table | Clusters rows by a numeric column using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan('orders', 'amount', 0.5, 5, 'clusters');] |
| anofox_tab_dbscan_mv | table | Clusters rows by multiple numeric columns (comma-separated) using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan_mv('orders', 'amount,qty', 0.5, 5, 'clusters');] |
| anofox_tab_diff_hashdiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]). Currently identical to diff_joindiff; the bisection_threshold/bisection_factor parameters are not implemented and rejected. | NULL | [SELECT * FROM diff_hashdiff('products_v1', 'products_v2', 'product_id');, SELECT * FROM diff_hashdiff('products_v1', 'products_v2', ['product_id', 'sku']);] |
| anofox_tab_diff_joindiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]), returning rows that were added, removed, or changed. Primary keys are matched NULL-safely (IS NOT DISTINCT FROM). | NULL | [SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', 'order_id');, SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', ['order_id', 'line_id']);] |
| anofox_tab_distinct_count | table | Asserts that the number of distinct values in a column is between min_distinct and max_distinct. | NULL | [SELECT * FROM distinct_count('orders', 'status', 1, 10);] |
| anofox_tab_email_config | table | Returns the current configuration settings for the email validation module. | NULL | [SELECT * FROM email_config();] |
| anofox_tab_email_is_valid | scalar | Returns TRUE if the email address passes validation using the specified mode ('regex', 'dns', or 'smtp'). Defaults to the configured mode. | NULL | [SELECT email_is_valid('[email protected]');, SELECT email_is_valid('[email protected]', 'smtp');] |
| anofox_tab_email_validate | scalar | Validates an email address using the specified validation mode ('regex', 'dns', or 'smtp'). Returns a struct with validation details. Defaults to the configured mode. | NULL | [SELECT email_validate('[email protected]');, SELECT email_validate('[email protected]', 'dns');] |
| anofox_tab_freshness | table | Returns rows where the most recent value in a timestamp column is older than the specified maximum age interval. Optionally accepts a reference time. | NULL | [SELECT * FROM freshness('events', 'created_at', INTERVAL '1 day');] |
| anofox_tab_iqr | table | Identifies rows where a numeric column value is an outlier by the IQR (interquartile range) method. | NULL | [SELECT * FROM iqr('orders', 'amount', 1.5);] |
| anofox_tab_is_valid_currency | scalar | Returns TRUE if the given string is a valid ISO 4217 currency code. | NULL | [SELECT is_valid_currency('USD');] |
| anofox_tab_is_valid_vat_country | scalar | Returns TRUE if the 2-letter string is a country that uses VAT numbers. | NULL | [SELECT is_valid_vat_country('DE');] |
| anofox_tab_isolation_forest | table | Detects univariate outliers in a numeric column using the Isolation Forest algorithm. Returns scores and outlier labels. | NULL | [SELECT * FROM isolation_forest('sales', 'amount', 100, 256, 0.05, 'scores');] |
| anofox_tab_isolation_forest_mv | table | Detects multivariate outliers across multiple numeric columns (comma-separated) using the Isolation Forest algorithm. | NULL | [SELECT * FROM isolation_forest_mv('sales', 'amount,qty', 100, 256, 0.05, 'scores');] |
| anofox_tab_money | scalar | Creates a money value from a decimal amount and ISO 4217 currency code. | NULL | [SELECT money(19.99, 'USD');] |
| anofox_tab_money_abs | scalar | Returns the absolute value of a money amount. | NULL | [SELECT money_abs(money(-19.99, 'USD'));] |
| anofox_tab_money_add | scalar | Adds two money values of the same currency. | NULL | [SELECT money_add(money(10.00, 'USD'), money(5.00, 'USD'));] |
| anofox_tab_money_amount | scalar | Extracts the exact numeric amount (DECIMAL(18,3)) from a money value. | NULL | [SELECT money_amount(money(19.99, 'USD'));] |
| anofox_tab_money_currency | scalar | Extracts the ISO 4217 currency code from a money value. | NULL | [SELECT money_currency(money(19.99, 'USD'));] |
| anofox_tab_money_format | scalar | Formats a money value as a string using the specified style ('symbol', 'code', or 'plain'). | NULL | [SELECT money_format(money(19.99, 'USD'), 'symbol');] |
| anofox_tab_money_from_cents | scalar | Creates a money value from an integer amount in the smallest currency unit (e.g. cents) and ISO 4217 currency code. | NULL | [SELECT money_from_cents(1999, 'USD');] |
| anofox_tab_money_in_range | scalar | Returns TRUE if the money amount is within the inclusive range [min, max]. | NULL | [SELECT money_in_range(money(15.00, 'USD'), 10.0, 20.0);] |
| anofox_tab_money_is_negative | scalar | Returns TRUE if the money amount is less than zero. | NULL | [SELECT money_is_negative(money(-5.00, 'USD'));] |
| anofox_tab_money_is_positive | scalar | Returns TRUE if the money amount is greater than zero. | NULL | [SELECT money_is_positive(money(19.99, 'USD'));] |
| anofox_tab_money_is_zero | scalar | Returns TRUE if the money amount is exactly zero. | NULL | [SELECT money_is_zero(money(0.00, 'USD'));] |
| anofox_tab_money_multiply | scalar | Multiplies a money amount by a scalar factor. | NULL | [SELECT money_multiply(money(10.00, 'USD'), 1.5);] |
| anofox_tab_money_same_currency | scalar | Returns TRUE if two money values have the same currency code. | NULL | [SELECT money_same_currency(money(10.00, 'USD'), money(5.00, 'USD'));] |
| anofox_tab_money_subtract | scalar | Subtracts the second money value from the first (must be the same currency). | NULL | [SELECT money_subtract(money(10.00, 'USD'), money(3.00, 'USD'));] |
| anofox_tab_null_rate | table | Asserts that the fraction of NULL values in a column does not exceed max_null_rate. | NULL | [SELECT * FROM null_rate('orders', 'email', 0.05);] |
| anofox_tab_outlier_tree | table | Identifies statistical outliers in a table using the OutlierTree algorithm, returning an explanation of which conditions make each row an outlier. | NULL | [SELECT * FROM outlier_tree('transactions', 'amount,balance', 'summary');] |
| anofox_tab_phonenumber_example | scalar | Returns an example valid phone number for the given 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_example('US');] |
| anofox_tab_phonenumber_format | scalar | Formats a phone number string in the specified format: 'E164', 'INTERNATIONAL', 'NATIONAL', or 'RFC3966'. | NULL | [SELECT phonenumber_format('+14155552671', 'NATIONAL');] |
| anofox_tab_phonenumber_is_possible | scalar | Returns TRUE if the phone number length is plausible for its region (lighter check than is_valid). | NULL | [SELECT phonenumber_is_possible('+14155552671');] |
| anofox_tab_phonenumber_is_valid | scalar | Returns TRUE if the phone number is a valid, dialable number. | NULL | [SELECT phonenumber_is_valid('+14155552671');] |
| anofox_tab_phonenumber_is_valid_for_region | scalar | Returns TRUE if the phone number is valid for the specified 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_is_valid_for_region('+14155552671', 'US');] |
| anofox_tab_phonenumber_match | scalar | Returns TRUE if two phone number strings refer to the same number. | NULL | [SELECT phonenumber_match('+14155552671', '(415) 555-2671');] |
| anofox_tab_phonenumber_parse | scalar | Parses a phone number string and returns a struct with E164, national, international, and RFC3966 formats, plus the region code. | NULL | [SELECT phonenumber_parse('+14155552671');] |
| anofox_tab_phonenumber_region | scalar | Returns the 2-letter ISO region code (e.g., 'US', 'DE') for a given phone number. | NULL | [SELECT phonenumber_region('+14155552671');] |
| anofox_tab_phonenumber_status | table | Returns the current configuration and status of the phone number module. | NULL | [SELECT * FROM phonenumber_status();] |
| anofox_tab_pii_audit_table | table | Returns a row-level audit of PII detected in all string columns of a table. | NULL | [SELECT * FROM pii_audit_table('customers');] |
| anofox_tab_pii_config | table | Returns the current configuration settings of the PII detection module. | NULL | [SELECT * FROM pii_config();] |
| anofox_tab_pii_contains | scalar | Returns TRUE if the text contains any PII of the specified type (e.g., 'email', 'phone', 'ssn'). | NULL | [SELECT pii_contains('Contact us at [email protected]', 'email');] |
| anofox_tab_pii_count | scalar | Returns the total count of PII entities detected in the text. | NULL | [SELECT pii_count('John at [email protected], tel: +1-555-0100');] |
| anofox_tab_pii_detect | scalar | Detects all PII entities in a text string and returns them as a list of structs with type, value, start, and end positions. | NULL | [SELECT pii_detect('Contact John at [email protected] or +1-555-123-4567');] |
| anofox_tab_pii_detect_batch | scalar | Runs all PII detectors on an array of text strings and returns a combined list of all detected entities per input. | NULL | [SELECT pii_detect_batch(['[email protected]', 'SSN: 123-45-6789']);] |
| anofox_tab_pii_detect_credit_cards | scalar | Detects all credit card numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_credit_cards('Pay with 4111111111111111');] |
| anofox_tab_pii_detect_emails | scalar | Detects all email addresses in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_emails('Contact [email protected] or [email protected]');] |
| anofox_tab_pii_detect_ibans | scalar | Detects all IBAN numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ibans('Bank: DE89370400440532013000');] |
| anofox_tab_pii_detect_names | scalar | Detects all person names in the text using NLP and returns them as a list of structs. | NULL | [SELECT pii_detect_names('Signed by John Smith and Jane Doe');] |
| anofox_tab_pii_detect_phones | scalar | Detects all phone numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_phones('Call +1-555-0100 or 0800 123 456');] |
| anofox_tab_pii_detect_ssns | scalar | Detects all Social Security Numbers (SSNs) in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ssns('SSN: 123-45-6789');] |
| anofox_tab_pii_is_valid_credit_card | scalar | Returns TRUE if the string is a valid credit card number (uses Luhn algorithm). | NULL | [SELECT pii_is_valid_credit_card('4111111111111111');] |
| anofox_tab_pii_is_valid_crypto_address | scalar | Returns TRUE if the string is a valid cryptocurrency wallet address (Bitcoin, Ethereum, etc.). | NULL | [SELECT pii_is_valid_crypto_address('1A1zP1eP5QGefi2DMPTfTL5SLmv7Divf');] |
| anofox_tab_pii_is_valid_de_tax_id | scalar | Returns TRUE if the string is a valid German tax identification number (Steueridentifikationsnummer). | NULL | [SELECT pii_is_valid_de_tax_id('12345678901');] |
| anofox_tab_pii_is_valid_iban | scalar | Returns TRUE if the string is a valid IBAN (International Bank Account Number). | NULL | [SELECT pii_is_valid_iban('DE89370400440532013000');] |
| anofox_tab_pii_is_valid_nino | scalar | Returns TRUE if the string is a valid UK National Insurance Number (NINO). | NULL | [SELECT pii_is_valid_nino('AB123456C');] |
| anofox_tab_pii_is_valid_ssn | scalar | Returns TRUE if the string is a valid US Social Security Number (SSN). | NULL | [SELECT pii_is_valid_ssn('123-45-6789');] |
| anofox_tab_pii_mask | scalar | Masks all detected PII in a text string using the specified strategy ('redact', 'hash', 'partial'). Defaults to 'redact'. | NULL | [SELECT pii_mask('Call me at +1-555-123-4567');, SELECT pii_mask('[email protected]', 'hash');] |
| anofox_tab_pii_mask_column | scalar | Masks a value of a specific PII type using the specified strategy ('redact', 'hash', 'partial'). | NULL | [SELECT pii_mask_column('[email protected]', 'email', 'hash');] |
| anofox_tab_pii_redact_column | scalar | Detects and redacts all PII in a text value using the specified strategy. Defaults to 'redact'. | NULL | [SELECT pii_redact_column('[email protected]');, SELECT pii_redact_column('[email protected]', 'hash');] |
| anofox_tab_pii_scan_table | table | Scans all string columns of a table and returns a summary of detected PII types per column. | NULL | [SELECT * FROM pii_scan_table('customers');] |
| anofox_tab_pii_status | table | Returns the current configuration and status of the PII detection module. | NULL | [SELECT * FROM pii_status();] |
| anofox_tab_postal_expand_address | scalar | Expands a postal address into all normalized variants using libpostal. | NULL | [SELECT postal_expand_address('123 main st springfield il');] |
| anofox_tab_postal_load_data | scalar | Downloads and installs the libpostal data bundle (~500 MB) required for address parsing and expansion. | NULL | [SELECT postal_load_data();] |
| anofox_tab_postal_parse_address | scalar | Parses a free-form postal address string into its structural components (house_number, road, city, state, postcode, country). | NULL | [SELECT postal_parse_address('123 Main St, Springfield, IL 62701');] |
| anofox_tab_postal_status | table | Returns the current status of the libpostal address parser, including whether it is initialized and the data directory path. | NULL | [SELECT * FROM postal_status();] |
| anofox_tab_profile_correlations | table | Computes pairwise Pearson correlation coefficients for numeric columns in a table. | NULL | [SELECT * FROM profile_correlations('orders');, SELECT * FROM profile_correlations('orders', ['amount', 'qty']);] |
| anofox_tab_profile_summary | table | Returns a summary profile of all columns in a table, including min, max, null_count, and distinct_count. | NULL | [SELECT * FROM profile_summary('orders');] |
| anofox_tab_profile_table | table | Profiles selected columns of a table with detailed statistics (min, max, mean, stddev, null_count, distinct_count, sample rows). Optionally filters columns, sets sample size, or uses exact counts. | NULL | [SELECT * FROM profile_table('orders');, SELECT * FROM profile_table('orders', ['amount', 'status']);] |
| anofox_tab_schema_check | table | Asserts that a table contains all the required column names. | NULL | [SELECT * FROM schema_check('orders', ['id', 'amount', 'status']);] |
| anofox_tab_vat | scalar | Parses a VAT number string and returns a struct with country code, normalized number, and validity flags. | NULL | [SELECT vat('DE123456789');] |
| anofox_tab_vat_country_name | scalar | Returns the English country name for the VAT number's country prefix (e.g., 'Germany'). | NULL | [SELECT vat_country_name('DE123456789');] |
| anofox_tab_vat_exists | scalar | Returns TRUE if the country prefix of the VAT number exists in the supported country list. | NULL | [SELECT vat_exists('DE123456789');] |
| anofox_tab_vat_format | scalar | Formats a VAT number using the specified style: 'plain' (digits only) or 'iso' (VAT country prefix + digits). | NULL | [SELECT vat_format('DE123456789', 'iso');] |
| anofox_tab_vat_is_eu_member | scalar | Returns TRUE if the VAT number belongs to an EU member state. | NULL | [SELECT vat_is_eu_member('DE123456789');] |
| anofox_tab_vat_is_valid | scalar | Returns TRUE if the VAT number has valid syntax and passes the country's check-digit validation where implemented. | NULL | [SELECT vat_is_valid('DE111111125');] |
| anofox_tab_vat_is_valid_syntax | scalar | Returns TRUE if the VAT number matches the expected syntax for its country prefix (no network check). | NULL | [SELECT vat_is_valid_syntax('DE123456789');] |
| anofox_tab_vat_normalize | scalar | Normalizes a VAT number by removing spaces, dashes, and other formatting characters. | NULL | [SELECT vat_normalize('DE 123 456 789');] |
| anofox_tab_vat_split | scalar | Splits a VAT number into a struct with 'country_code' and 'number' fields. | NULL | [SELECT vat_split('DE123456789');] |
| anofox_tab_volume | table | Asserts that a table has between min_rows and max_rows rows, returning the count and assertion status. | NULL | [SELECT * FROM volume('orders', 100, 1000000);] |
| anofox_tab_zscore | table | Identifies rows where a numeric column value deviates more than threshold standard deviations from the mean. | NULL | [SELECT * FROM zscore('orders', 'amount', 3.0);] |
| currency_name | scalar | Returns the full English name of a currency given its ISO 4217 code (e.g., 'US Dollar'). | NULL | [SELECT currency_name('EUR');] |
| currency_symbol | scalar | Returns the currency symbol for a given ISO 4217 currency code (e.g., '$' for USD). | NULL | [SELECT currency_symbol('EUR');] |
| dbscan | table | Clusters rows by a numeric column using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan('orders', 'amount', 0.5, 5, 'clusters');] |
| dbscan_mv | table | Clusters rows by multiple numeric columns (comma-separated) using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan_mv('orders', 'amount,qty', 0.5, 5, 'clusters');] |
| diff_hashdiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]). Currently identical to diff_joindiff; the bisection_threshold/bisection_factor parameters are not implemented and rejected. | NULL | [SELECT * FROM diff_hashdiff('products_v1', 'products_v2', 'product_id');, SELECT * FROM diff_hashdiff('products_v1', 'products_v2', ['product_id', 'sku']);] |
| diff_joindiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]), returning rows that were added, removed, or changed. Primary keys are matched NULL-safely (IS NOT DISTINCT FROM). | NULL | [SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', 'order_id');, SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', ['order_id', 'line_id']);] |
| distinct_count | table | Asserts that the number of distinct values in a column is between min_distinct and max_distinct. | NULL | [SELECT * FROM distinct_count('orders', 'status', 1, 10);] |
| email_config | table | Returns the current configuration settings for the email validation module. | NULL | [SELECT * FROM email_config();] |
| email_is_valid | scalar | Returns TRUE if the email address passes validation using the specified mode ('regex', 'dns', or 'smtp'). Defaults to the configured mode. | NULL | [SELECT email_is_valid('[email protected]');, SELECT email_is_valid('[email protected]', 'smtp');] |
| email_validate | scalar | Validates an email address using the specified validation mode ('regex', 'dns', or 'smtp'). Returns a struct with validation details. Defaults to the configured mode. | NULL | [SELECT email_validate('[email protected]');, SELECT email_validate('[email protected]', 'dns');] |
| freshness | table | NULL | NULL | |
| iqr | table | Identifies rows where a numeric column value is an outlier by the IQR (interquartile range) method. | NULL | [SELECT * FROM iqr('orders', 'amount', 1.5);] |
| is_valid_currency | scalar | Returns TRUE if the given string is a valid ISO 4217 currency code. | NULL | [SELECT is_valid_currency('USD');] |
| is_valid_vat_country | scalar | Returns TRUE if the 2-letter string is a country that uses VAT numbers. | NULL | [SELECT is_valid_vat_country('DE');] |
| isolation_forest | table | Detects univariate outliers in a numeric column using the Isolation Forest algorithm. Returns scores and outlier labels. | NULL | [SELECT * FROM isolation_forest('sales', 'amount', 100, 256, 0.05, 'scores');] |
| isolation_forest_mv | table | Detects multivariate outliers across multiple numeric columns (comma-separated) using the Isolation Forest algorithm. | NULL | [SELECT * FROM isolation_forest_mv('sales', 'amount,qty', 100, 256, 0.05, 'scores');] |
| money | scalar | Creates a money value from a decimal amount and ISO 4217 currency code. | NULL | [SELECT money(19.99, 'USD');] |
| money_abs | scalar | Returns the absolute value of a money amount. | NULL | [SELECT money_abs(money(-19.99, 'USD'));] |
| money_add | scalar | Adds two money values of the same currency. | NULL | [SELECT money_add(money(10.00, 'USD'), money(5.00, 'USD'));] |
| money_amount | scalar | Extracts the exact numeric amount (DECIMAL(18,3)) from a money value. | NULL | [SELECT money_amount(money(19.99, 'USD'));] |
| money_currency | scalar | Extracts the ISO 4217 currency code from a money value. | NULL | [SELECT money_currency(money(19.99, 'USD'));] |
| money_format | scalar | Formats a money value as a string using the specified style ('symbol', 'code', or 'plain'). | NULL | [SELECT money_format(money(19.99, 'USD'), 'symbol');] |
| money_from_cents | scalar | Creates a money value from an integer amount in the smallest currency unit (e.g. cents) and ISO 4217 currency code. | NULL | [SELECT money_from_cents(1999, 'USD');] |
| money_in_range | scalar | Returns TRUE if the money amount is within the inclusive range [min, max]. | NULL | [SELECT money_in_range(money(15.00, 'USD'), 10.0, 20.0);] |
| money_is_negative | scalar | Returns TRUE if the money amount is less than zero. | NULL | [SELECT money_is_negative(money(-5.00, 'USD'));] |
| money_is_positive | scalar | Returns TRUE if the money amount is greater than zero. | NULL | [SELECT money_is_positive(money(19.99, 'USD'));] |
| money_is_zero | scalar | Returns TRUE if the money amount is exactly zero. | NULL | [SELECT money_is_zero(money(0.00, 'USD'));] |
| money_multiply | scalar | Multiplies a money amount by a scalar factor. | NULL | [SELECT money_multiply(money(10.00, 'USD'), 1.5);] |
| money_same_currency | scalar | Returns TRUE if two money values have the same currency code. | NULL | [SELECT money_same_currency(money(10.00, 'USD'), money(5.00, 'USD'));] |
| money_subtract | scalar | Subtracts the second money value from the first (must be the same currency). | NULL | [SELECT money_subtract(money(10.00, 'USD'), money(3.00, 'USD'));] |
| null_rate | table | Asserts that the fraction of NULL values in a column does not exceed max_null_rate. | NULL | [SELECT * FROM null_rate('orders', 'email', 0.05);] |
| outlier_tree | table | NULL | NULL | |
| phonenumber_example | scalar | Returns an example valid phone number for the given 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_example('US');] |
| phonenumber_format | scalar | Formats a phone number string in the specified format: 'E164', 'INTERNATIONAL', 'NATIONAL', or 'RFC3966'. | NULL | [SELECT phonenumber_format('+14155552671', 'NATIONAL');] |
| phonenumber_is_possible | scalar | Returns TRUE if the phone number length is plausible for its region (lighter check than is_valid). | NULL | [SELECT phonenumber_is_possible('+14155552671');] |
| phonenumber_is_valid | scalar | Returns TRUE if the phone number is a valid, dialable number. | NULL | [SELECT phonenumber_is_valid('+14155552671');] |
| phonenumber_is_valid_for_region | scalar | Returns TRUE if the phone number is valid for the specified 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_is_valid_for_region('+14155552671', 'US');] |
| phonenumber_match | scalar | Returns TRUE if two phone number strings refer to the same number. | NULL | [SELECT phonenumber_match('+14155552671', '(415) 555-2671');] |
| phonenumber_parse | scalar | Parses a phone number string and returns a struct with E164, national, international, and RFC3966 formats, plus the region code. | NULL | [SELECT phonenumber_parse('+14155552671');] |
| phonenumber_region | scalar | Returns the 2-letter ISO region code (e.g., 'US', 'DE') for a given phone number. | NULL | [SELECT phonenumber_region('+14155552671');] |
| phonenumber_status | table | Returns the current configuration and status of the phone number module. | NULL | [SELECT * FROM phonenumber_status();] |
| pii_audit_table | table | Returns a row-level audit of PII detected in all string columns of a table. | NULL | [SELECT * FROM pii_audit_table('customers');] |
| pii_config | table | Returns the current configuration settings of the PII detection module. | NULL | [SELECT * FROM pii_config();] |
| pii_contains | scalar | Returns TRUE if the text contains any PII of the specified type (e.g., 'email', 'phone', 'ssn'). | NULL | [SELECT pii_contains('Contact us at [email protected]', 'email');] |
| pii_count | scalar | Returns the total count of PII entities detected in the text. | NULL | [SELECT pii_count('John at [email protected], tel: +1-555-0100');] |
| pii_detect | scalar | Detects all PII entities in a text string and returns them as a list of structs with type, value, start, and end positions. | NULL | [SELECT pii_detect('Contact John at [email protected] or +1-555-123-4567');] |
| pii_detect_batch | scalar | Runs all PII detectors on an array of text strings and returns a combined list of all detected entities per input. | NULL | [SELECT pii_detect_batch(['[email protected]', 'SSN: 123-45-6789']);] |
| pii_detect_credit_cards | scalar | Detects all credit card numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_credit_cards('Pay with 4111111111111111');] |
| pii_detect_emails | scalar | Detects all email addresses in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_emails('Contact [email protected] or [email protected]');] |
| pii_detect_ibans | scalar | Detects all IBAN numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ibans('Bank: DE89370400440532013000');] |
| pii_detect_names | scalar | Detects all person names in the text using NLP and returns them as a list of structs. | NULL | [SELECT pii_detect_names('Signed by John Smith and Jane Doe');] |
| pii_detect_phones | scalar | Detects all phone numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_phones('Call +1-555-0100 or 0800 123 456');] |
| pii_detect_ssns | scalar | Detects all Social Security Numbers (SSNs) in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ssns('SSN: 123-45-6789');] |
| pii_is_valid_credit_card | scalar | Returns TRUE if the string is a valid credit card number (uses Luhn algorithm). | NULL | [SELECT pii_is_valid_credit_card('4111111111111111');] |
| pii_is_valid_crypto_address | scalar | Returns TRUE if the string is a valid cryptocurrency wallet address (Bitcoin, Ethereum, etc.). | NULL | [SELECT pii_is_valid_crypto_address('1A1zP1eP5QGefi2DMPTfTL5SLmv7Divf');] |
| pii_is_valid_de_tax_id | scalar | Returns TRUE if the string is a valid German tax identification number (Steueridentifikationsnummer). | NULL | [SELECT pii_is_valid_de_tax_id('12345678901');] |
| pii_is_valid_iban | scalar | Returns TRUE if the string is a valid IBAN (International Bank Account Number). | NULL | [SELECT pii_is_valid_iban('DE89370400440532013000');] |
| pii_is_valid_nino | scalar | Returns TRUE if the string is a valid UK National Insurance Number (NINO). | NULL | [SELECT pii_is_valid_nino('AB123456C');] |
| pii_is_valid_ssn | scalar | Returns TRUE if the string is a valid US Social Security Number (SSN). | NULL | [SELECT pii_is_valid_ssn('123-45-6789');] |
| pii_mask | scalar | Masks all detected PII in a text string using the specified strategy ('redact', 'hash', 'partial'). Defaults to 'redact'. | NULL | [SELECT pii_mask('Call me at +1-555-123-4567');, SELECT pii_mask('[email protected]', 'hash');] |
| pii_mask_column | scalar | Masks a value of a specific PII type using the specified strategy ('redact', 'hash', 'partial'). | NULL | [SELECT pii_mask_column('[email protected]', 'email', 'hash');] |
| pii_redact_column | scalar | Detects and redacts all PII in a text value using the specified strategy. Defaults to 'redact'. | NULL | [SELECT pii_redact_column('[email protected]');, SELECT pii_redact_column('[email protected]', 'hash');] |
| pii_scan_table | table | Scans all string columns of a table and returns a summary of detected PII types per column. | NULL | [SELECT * FROM pii_scan_table('customers');] |
| pii_status | table | Returns the current configuration and status of the PII detection module. | NULL | [SELECT * FROM pii_status();] |
| postal_expand_address | scalar | Expands a postal address into all normalized variants using libpostal. | NULL | [SELECT postal_expand_address('123 main st springfield il');] |
| postal_load_data | scalar | Downloads and installs the libpostal data bundle (~500 MB) required for address parsing and expansion. | NULL | [SELECT postal_load_data();] |
| postal_parse_address | scalar | Parses a free-form postal address string into its structural components (house_number, road, city, state, postcode, country). | NULL | [SELECT postal_parse_address('123 Main St, Springfield, IL 62701');] |
| postal_status | table | Returns the current status of the libpostal address parser, including whether it is initialized and the data directory path. | NULL | [SELECT * FROM postal_status();] |
| profile_correlations | table | Computes pairwise Pearson correlation coefficients for numeric columns in a table. | NULL | [SELECT * FROM profile_correlations('orders');, SELECT * FROM profile_correlations('orders', ['amount', 'qty']);] |
| profile_summary | table | Returns a summary profile of all columns in a table, including min, max, null_count, and distinct_count. | NULL | [SELECT * FROM profile_summary('orders');] |
| profile_table | table | Profiles selected columns of a table with detailed statistics (min, max, mean, stddev, null_count, distinct_count, sample rows). Optionally filters columns, sets sample size, or uses exact counts. | NULL | [SELECT * FROM profile_table('orders');, SELECT * FROM profile_table('orders', ['amount', 'status']);] |
| schema_check | table | Asserts that a table contains all the required column names. | NULL | [SELECT * FROM schema_check('orders', ['id', 'amount', 'status']);] |
| vat | scalar | Parses a VAT number string and returns a struct with country code, normalized number, and validity flags. | NULL | [SELECT vat('DE123456789');] |
| vat_country_name | scalar | Returns the English country name for the VAT number's country prefix (e.g., 'Germany'). | NULL | [SELECT vat_country_name('DE123456789');] |
| vat_exists | scalar | Returns TRUE if the country prefix of the VAT number exists in the supported country list. | NULL | [SELECT vat_exists('DE123456789');] |
| vat_format | scalar | Formats a VAT number using the specified style: 'plain' (digits only) or 'iso' (VAT country prefix + digits). | NULL | [SELECT vat_format('DE123456789', 'iso');] |
| vat_is_eu_member | scalar | Returns TRUE if the VAT number belongs to an EU member state. | NULL | [SELECT vat_is_eu_member('DE123456789');] |
| vat_is_valid | scalar | Returns TRUE if the VAT number has valid syntax and passes the country's check-digit validation where implemented. | NULL | [SELECT vat_is_valid('DE111111125');] |
| vat_is_valid_syntax | scalar | Returns TRUE if the VAT number matches the expected syntax for its country prefix (no network check). | NULL | [SELECT vat_is_valid_syntax('DE123456789');] |
| vat_normalize | scalar | Normalizes a VAT number by removing spaces, dashes, and other formatting characters. | NULL | [SELECT vat_normalize('DE 123 456 789');] |
| vat_split | scalar | Splits a VAT number into a struct with 'country_code' and 'number' fields. | NULL | [SELECT vat_split('DE123456789');] |
| volume | table | Asserts that a table has between min_rows and max_rows rows, returning the count and assertion status. | NULL | [SELECT * FROM volume('orders', 100, 1000000);] |
| zscore | table | Identifies rows where a numeric column value deviates more than threshold standard deviations from the mean. | NULL | [SELECT * FROM zscore('orders', 'amount', 3.0);] |
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 |
|---|---|---|---|---|
| anofox_ner_cache_size | LRU cache size for NER results (0 to disable) | BIGINT | GLOBAL | [] |
| anofox_ner_device | OpenVINO device for NER inference: AUTO (default), CPU, GPU, GPU.0, etc. | VARCHAR | GLOBAL | [] |
| anofox_ner_model | NER model to use (supported: distilbert-en); must be set before the model is first loaded | VARCHAR | GLOBAL | [] |
| anofox_pii_deep_validation | Enable deep validation using libphonenumber for phone numbers (default: false) | BOOLEAN | GLOBAL | [] |
| anofox_pii_default_mask_strategy | Default masking strategy (REDACT, HASH, PARTIAL, ASTERISK, NONE) | VARCHAR | GLOBAL | [] |
| anofox_pii_enabled_types | Comma-separated list of PII types to detect (empty = all) | VARCHAR | GLOBAL | [] |
| anofox_pii_min_confidence | Minimum confidence threshold for NER-based PII detection (0.0 - 1.0) | DOUBLE | GLOBAL | [] |
| anofox_tab_email_default_validation | Default validation mode for anofox_tab_email_is_valid (regex, dns, smtp) | VARCHAR | GLOBAL | [] |
| anofox_tab_email_dns_timeout_ms | DNS resolver timeout in milliseconds | BIGINT | GLOBAL | [] |
| anofox_tab_email_dns_tries | Number of DNS queries to attempt before failing (1-10) | INTEGER | GLOBAL | [] |
| anofox_tab_email_regex_pattern | Regular expression used during email regex validation | VARCHAR | GLOBAL | [] |
| anofox_tab_email_smtp_connect_timeout_ms | SMTP connect timeout in milliseconds | BIGINT | GLOBAL | [] |
| anofox_tab_email_smtp_helo_domain | Domain value used during SMTP EHLO negotiation | VARCHAR | GLOBAL | [] |
| anofox_tab_email_smtp_mail_from | MAIL FROM address presented during SMTP verification | VARCHAR | GLOBAL | [] |
| anofox_tab_email_smtp_port | SMTP port used when connecting to MX hosts | INTEGER | GLOBAL | [] |
| anofox_tab_email_smtp_read_timeout_ms | SMTP read/write timeout in milliseconds | BIGINT | GLOBAL | [] |
| anofox_tab_phonenumber_default_region | Default region code used when the region hint is NULL | VARCHAR | GLOBAL | [] |
| anofox_tab_postal_data_path | Directory storing libpostal assets | VARCHAR | GLOBAL | [] |
| anofox_tab_trace_enabled | Enable anofox tracing output | BOOLEAN | GLOBAL | [] |
| anofox_tab_trace_level | Minimum tracing level (trace/debug/info/warn/error/critical/off) | VARCHAR | GLOBAL | [] |
| anofox_telemetry_enabled | Enable or disable anonymous usage telemetry | BOOLEAN | GLOBAL | [] |
| anofox_telemetry_key | PostHog API key for telemetry | VARCHAR | GLOBAL | [] |