ERPL-Web Functions Reference

Complete reference for all ERPL-Web functions with signatures, parameters, and return values.

HTTP Functions

http_get

Make an HTTP GET request.

Signature:

http_get(
  url VARCHAR,
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
) → TABLE

Parameters:

url: Request URL
headers: Custom HTTP headers
accept: Accept header value
auth: Authentication credential (username:password for BASIC, token for BEARER)
auth_type: 'BASIC' or 'BEARER'
timeout: Timeout in milliseconds

Returns: Table with columns:

method (VARCHAR)
status (INTEGER)
url (VARCHAR)
headers (MAP(VARCHAR, VARCHAR))
content_type (VARCHAR)
content (VARCHAR)

http_post

Make an HTTP POST request.

Signature:

http_post(
  url VARCHAR,
  body VARCHAR,
  content_type VARCHAR := 'application/json',
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
) → TABLE

Parameters:

url: Request URL
body: Request body content
content_type: Content-Type header
headers: Custom HTTP headers
accept: Accept header value
auth: Authentication credential
auth_type: 'BASIC' or 'BEARER'
timeout: Timeout in milliseconds

Returns: Same as http_get

http_put

Make an HTTP PUT request.

Signature:

http_put(
  url VARCHAR,
  body VARCHAR,
  content_type VARCHAR := 'application/json',
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
) → TABLE

Parameters: Same as http_post

Returns: Same as http_get

http_patch

Make an HTTP PATCH request.

Signature:

http_patch(
  url VARCHAR,
  body VARCHAR,
  content_type VARCHAR := 'application/json',
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
) → TABLE

Parameters: Same as http_post

Returns: Same as http_get

http_delete

Make an HTTP DELETE request.

Signature:

http_delete(
  url VARCHAR,
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
) → TABLE

Parameters: Same as http_get (no body)

Returns: Same as http_get

OData Functions

ATTACH (OData)

Attach an OData service as a database.

Signature:

ATTACH 'service_url' AS database_name (TYPE odata);

Parameters:

service_url: OData service root URL
database_name: Alias for the attached database

Example:

ATTACH 'https://services.odata.org/TripPinRESTierService' 
  AS trippin (TYPE odata);

odata_read

Read from an OData entity set.

Signature:

odata_read(
  entity_set_url VARCHAR,
  secret VARCHAR := NULL,
  expand VARCHAR := NULL
) → TABLE

Parameters:

entity_set_url: Full URL to OData entity set
secret: DuckDB secret name for authentication
expand: OData $expand parameter for navigation properties

Returns: Table with columns matching the OData entity schema

Example:

SELECT * FROM odata_read(
  'https://services.odata.org/TripPinRESTierService/People',
  expand := 'Trips'
);

Datasphere Functions

datasphere_show_spaces

List all accessible Datasphere spaces.

Signature:

datasphere_show_spaces(
  secret VARCHAR := NULL
) → TABLE

Parameters:

secret: DuckDB secret name (optional, uses default if not specified)

Returns: Table with column:

name (VARCHAR): Space ID

datasphere_show_assets

List assets in a space or across all spaces.

Signatures:

-- Assets in specific space
datasphere_show_assets(
  space_id VARCHAR,
  secret VARCHAR := NULL
) → TABLE

-- All accessible assets
datasphere_show_assets(
  secret VARCHAR := NULL
) → TABLE

Parameters:

space_id: Space identifier (optional)
secret: DuckDB secret name

Returns: Table with columns:

name (VARCHAR): Asset label
object_type (VARCHAR): Type (View, Table, etc.)
technical_name (VARCHAR): Technical identifier
space_name (VARCHAR): Space ID (only when querying all spaces)

datasphere_describe_space

Get detailed metadata for a space.

Signature:

datasphere_describe_space(
  space_id VARCHAR,
  secret VARCHAR := NULL
) → TABLE

Parameters:

space_id: Space identifier
secret: DuckDB secret name

Returns: Table with columns:

name (VARCHAR): Space ID
label (VARCHAR): Display label

datasphere_describe_asset

Get comprehensive metadata for an asset.

Signature:

datasphere_describe_asset(
  space_id VARCHAR,
  asset_id VARCHAR,
  secret VARCHAR := NULL
) → TABLE

Parameters:

space_id: Space identifier
asset_id: Asset technical name
secret: DuckDB secret name

Returns: Table with 15 columns including:

name (VARCHAR)
space_name (VARCHAR)
label (VARCHAR)
asset_type (VARCHAR)
asset_relational_metadata_url (VARCHAR)
asset_relational_data_url (VARCHAR)
asset_analytical_metadata_url (VARCHAR)
asset_analytical_data_url (VARCHAR)
supports_analytical_queries (BOOLEAN)
has_relational_access (BOOLEAN)
has_analytical_access (BOOLEAN)
relational_schema (STRUCT)
analytical_schema (STRUCT)
odata_context (VARCHAR)
odata_metadata_etag (VARCHAR)

datasphere_read_relational

Query relational data from a Datasphere asset.

Signature:

datasphere_read_relational(
  space_id VARCHAR,
  asset_id VARCHAR,
  secret VARCHAR := NULL,
  top BIGINT := NULL,
  skip BIGINT := NULL,
  params MAP(VARCHAR, VARCHAR) := NULL
) → TABLE

Parameters:

space_id: Space identifier
asset_id: Asset technical name
secret: DuckDB secret name
top: Limit number of rows (OData $top)
skip: Skip rows (OData $skip)
params: Input parameters for parameterized views

Returns: Table with columns matching the asset schema

datasphere_read_analytical

Query analytical data from a Datasphere asset.

Signature:

datasphere_read_analytical(
  space_id VARCHAR,
  asset_id VARCHAR,
  secret VARCHAR := NULL,
  top BIGINT := NULL,
  skip BIGINT := NULL,
  params MAP(VARCHAR, VARCHAR) := NULL,
  metrics LIST(VARCHAR) := NULL,
  dimensions LIST(VARCHAR) := NULL
) → TABLE

Parameters:

space_id: Space identifier
asset_id: Asset technical name
secret: DuckDB secret name
top: Limit rows
skip: Skip rows
params: Input parameters
metrics: List of measures to retrieve
dimensions: List of dimensions to retrieve

Returns: Table with columns matching requested metrics and dimensions

ODP Functions

odp_odata_show

Discover available ODP OData services.

Signature:

odp_odata_show(
  base_url VARCHAR,
  secret VARCHAR := NULL
) → TABLE

Parameters:

base_url: SAP server base URL (e.g., https://sap-server:port)
secret: DuckDB secret name for authentication

Returns: Table with columns:

service_name (VARCHAR)
entity_set_name (VARCHAR)
full_entity_set_url (VARCHAR)
description (VARCHAR)

odp_odata_read

Extract data from ODP OData with automatic delta replication.

Signature:

odp_odata_read(
  entity_set_url VARCHAR,
  secret VARCHAR := NULL
) → TABLE

Parameters:

entity_set_url: Full URL to ODP OData entity set
secret: DuckDB secret name for authentication

Returns: Table with columns matching entity schema plus:

RECORD_MODE (VARCHAR): Change indicator ('' = update, 'N' = insert, 'D' = delete)
ODQ_CHANGEMODE (VARCHAR)
ODQ_ENTITYCNTR (VARCHAR)

Behavior:

First call: Creates subscription and performs initial full load
Subsequent calls: Returns only delta changes since last extraction

odp_odata_list_subscriptions

List all active ODP subscriptions.

Signature:

odp_odata_list_subscriptions() → TABLE

Parameters: None

Returns: Table with columns:

subscription_id (VARCHAR)
entity_set_name (VARCHAR)
entity_set_url (VARCHAR)
subscription_status (VARCHAR)
last_delta_token (VARCHAR)
created_at (TIMESTAMP)
updated_at (TIMESTAMP)

Pragma Functions

odp_odata_remove_subscription

Remove an ODP subscription.

Signature:

PRAGMA odp_odata_remove_subscription(
  subscription_id VARCHAR,
  delete_on_server BOOLEAN
);

Parameters:

subscription_id: Subscription identifier
delete_on_server: If true, also deletes subscription on SAP server

Example:

-- Remove from local tracking only
PRAGMA odp_odata_remove_subscription('sub_123', false);

-- Remove from local AND SAP server
PRAGMA odp_odata_remove_subscription('sub_123', true);

Audit Tables

erpl_web.odp_subscription_audit

Audit log for all ODP extractions.

Schema:

subscription_id (VARCHAR)
request_timestamp (TIMESTAMP)
request_type (VARCHAR): 'INITIAL' or 'DELTA'
package_count (INTEGER)
records_received (BIGINT)
has_more_data (BOOLEAN)
new_delta_token (VARCHAR)
execution_time_ms (BIGINT)
package_size_bytes (BIGINT)
response_status (INTEGER)

Example:

SELECT * FROM erpl_web.odp_subscription_audit
WHERE subscription_id = 'sub_123'
ORDER BY request_timestamp DESC
LIMIT 10;

Configuration Settings

Tracing Settings

-- Enable/disable tracing
SET erpl_trace_enabled = TRUE|FALSE;

-- Trace level
SET erpl_trace_level = 'TRACE'|'DEBUG'|'INFO'|'WARN'|'ERROR';

-- Output destination
SET erpl_trace_output = 'console'|'file'|'both';

-- File configuration
SET erpl_trace_file_path = '/path/to/trace.log';
SET erpl_trace_max_file_size = 10485760;  -- bytes
SET erpl_trace_rotation = TRUE|FALSE;

Telemetry Settings

-- Enable/disable telemetry
SET erpl_telemetry_enabled = TRUE|FALSE;

-- Custom telemetry key
SET erpl_telemetry_key = 'your-posthog-key';

Common Parameter Patterns

Secret Parameter

Most functions accept an optional secret parameter:

-- Use default secret (auto-detected)
SELECT * FROM http_get('https://api.example.com');

-- Use named secret
SELECT * FROM http_get('https://api.example.com', secret := 'my_api');

Named Parameters

ERPL-Web functions use DuckDB's named parameter syntax:

-- Good: Named parameters (order doesn't matter)
SELECT * FROM datasphere_read_relational(
  'SALES',
  'CUSTOMERS',
  top := 100,
  skip := 50
);

-- Also works: Positional parameters (order matters)
SELECT * FROM datasphere_read_relational('SALES', 'CUSTOMERS', NULL, 100, 50);

MAP Parameters

-- Headers as MAP
SELECT * FROM http_get(
  'https://api.example.com',
  headers := {'Authorization': 'Bearer token', 'X-Custom': 'value'}
);

-- Params as MAP
SELECT * FROM datasphere_read_relational(
  'SALES',
  'VIEW',
  params := {'YEAR': '2024', 'REGION': 'EMEA'}
);

LIST Parameters

-- Metrics and dimensions as LIST
SELECT * FROM datasphere_read_analytical(
  'SALES',
  'ANALYTICS',
  metrics := ['Revenue', 'Cost'],
  dimensions := ['Region', 'Quarter']
);

Error Codes

Common HTTP status codes returned:

Code	Meaning	Action
200	Success	Continue
401	Unauthorized	Check credentials/secret
403	Forbidden	Verify permissions
404	Not Found	Check URL/entity name
429	Too Many Requests	Reduce request rate
500	Server Error	Check SAP/service logs
503	Service Unavailable	Retry later

Next Steps

Read detailed guides:
Check Examples for real-world usage

Summary

This reference covers all ERPL-Web functions:

5 HTTP functions for REST APIs
2 OData functions for OData services
6 Datasphere functions for SAP Datasphere
3 ODP functions for delta replication
Configuration settings for tracing and telemetry

For complete usage examples, see the individual function documentation pages.

HTTP Functions​

http_get​

http_post​

http_put​

http_patch​

http_delete​

OData Functions​

ATTACH (OData)​

odata_read​

Datasphere Functions​

datasphere_show_spaces​

datasphere_show_assets​

datasphere_describe_space​

datasphere_describe_asset​

datasphere_read_relational​

datasphere_read_analytical​

ODP Functions​

odp_odata_show​

odp_odata_read​

odp_odata_list_subscriptions​

Pragma Functions​

odp_odata_remove_subscription​

Audit Tables​

erpl_web.odp_subscription_audit​

Configuration Settings​

Tracing Settings​

Telemetry Settings​

Common Parameter Patterns​

Secret Parameter​

Named Parameters​

MAP Parameters​

LIST Parameters​

Error Codes​

Next Steps​

Summary​

HTTP Functions

http_get

http_post

http_put

http_patch

http_delete

OData Functions

ATTACH (OData)

odata_read

Datasphere Functions

datasphere_show_spaces

datasphere_show_assets

datasphere_describe_space

datasphere_describe_asset

datasphere_read_relational

datasphere_read_analytical

ODP Functions

odp_odata_show

odp_odata_read

odp_odata_list_subscriptions

Pragma Functions

odp_odata_remove_subscription

Audit Tables

erpl_web.odp_subscription_audit

Configuration Settings

Tracing Settings

Telemetry Settings

Common Parameter Patterns

Secret Parameter

Named Parameters

MAP Parameters

LIST Parameters

Error Codes

Next Steps

Summary