UPLOAD

Mark endpoint as a file upload handler.

Keywords

upload

Syntax

upload
upload for <handler_type>

If no handler is specified (only upload annotation without for), then the default handler will be used. The default handler is large_object unless configured otherwise via DefaultUploadHandler setting.

Handler Types

There are 4 handler types available:

Handler	Key	Description
Large Object	`large_object`	Stores files using PostgreSQL Large Objects API (default)
File System	`file_system`	Stores files on the server file system
CSV	`csv`	Parses CSV files and processes rows via PostgreSQL command
Excel	`excel`	Parses Excel files and processes rows via PostgreSQL command

Shared Annotation Options

These options are available for all handler types:

Option	Type	Default	Description
`stop_after_first_success`	bool	`false`	Stop upload after first successful upload when multiple handlers are used. Subsequent files will have status `Ignored`.
`included_mime_types`	string	`null`	CSV string of MIME type patterns to include. Set to `null` to allow all.
`excluded_mime_types`	string	`null`	CSV string of MIME type patterns to exclude. Set to `null` to exclude none.
`buffer_size`	int	`null`	Buffer size in bytes for raw content uploads (`large_object` and `file_system`).
`check_text`	bool	`false`	Validate file is a text file (not binary). Set to `true` to accept only text files.
`check_image`	bool/string	`false`	Validate file is an image. Set to `true` to accept only images, or CSV of allowed types: `jpg`, `png`, `gif`, `bmp`, `tiff`, `webp`.
`test_buffer_size`	int	`4096`	Buffer size in bytes when checking text files.
`non_printable_threshold`	int	`5`	Maximum non-printable characters allowed in test buffer to consider a valid text file.

Upload Metadata

All handlers return upload metadata as JSON with the following common properties:

Property	Type	Description
`type`	string	Handler type used (`large_object`, `file_system`, `csv`, `excel`)
`fileName`	string	Original uploaded file name
`contentType`	string	MIME type of the uploaded file
`size`	int	File size in bytes
`success`	bool	Whether the upload succeeded
`status`	string	Status message (e.g., `Ok`, `InvalidMimeType`)

Handler-specific properties:

Handler	Property	Type	Description
`large_object`	`oid`	int	PostgreSQL Large Object OID
`file_system`	`filePath`	string	Path where file was saved

Large Object Handler

Default handler that stores files using PostgreSQL Large Objects.

Basic Example

sql

create function lo_simple_upload(
    _meta json = null
)
returns json
language plpgsql
as
$$
begin
    return _meta;
end;
$$;

comment on function lo_simple_upload(json) is '
upload
param _meta is upload metadata
';

With Custom OID Parameter

You can specify a custom OID for the large object:

sql

create function lo_custom_parameter_upload(
    _oid bigint,
    _meta json = null
)
returns json
language plpgsql
as
$$
begin
    return _meta;
end;
$$;

comment on function lo_custom_parameter_upload(bigint, json) is '
upload for large_object
param _meta is upload metadata
oid = {_oid}
';

Context Metadata

Upload metadata is also available via PostgreSQL context setting:

sql

create function lo_simple_upload_context_metadata()
returns json
language plpgsql
as
$$
begin
    return current_setting('request.upload_metadata', true)::text;
end;
$$;

comment on function lo_simple_upload_context_metadata() is 'upload';

Large Object Annotation Options

All shared options plus:

Option	Description
`oid`	Custom OID for the large object
`large_object_included_mime_types`	Handler-specific MIME types to include
`large_object_excluded_mime_types`	Handler-specific MIME types to exclude
`large_object_buffer_size`	Handler-specific buffer size
`large_object_oid`	Handler-specific OID (alias for `oid`)
`large_object_check_text`	Handler-specific text check
`large_object_check_image`	Handler-specific image check
`large_object_test_buffer_size`	Handler-specific test buffer size
`large_object_non_printable_threshold`	Handler-specific non-printable threshold

File System Handler

Stores files on the server file system.

Basic Example

sql

create function fs_simple_upload(
    _meta json = null
)
returns json
language plpgsql
as
$$
begin
    return _meta;
end;
$$;

comment on function fs_simple_upload(json) is '
upload for file_system
param _meta is upload metadata
';

With Custom Parameters

Control the file path, name, and behavior:

sql

create function fs_custom_parameter_upload(
    _path text,
    _file text,
    _unique_name boolean,
    _create_path boolean,
    _meta json = null
)
returns json
language plpgsql
as
$$
begin
    return _meta;
end;
$$;

comment on function fs_custom_parameter_upload(text, text, boolean, boolean, json) is '
upload for file_system
param _meta is upload metadata
path = {_path}
file = {_file}
unique_name = {_unique_name}
create_path = {_create_path}
';

File System Annotation Options

All shared options plus:

Option	Description
`path`	Directory path for uploaded file
`file`	File name to use
`unique_name`	Generate unique file name (bool)
`create_path`	Create directory if not exists (bool)
`file_system_included_mime_types`	Handler-specific MIME types to include
`file_system_excluded_mime_types`	Handler-specific MIME types to exclude
`file_system_buffer_size`	Handler-specific buffer size
`file_system_path`	Handler-specific path (alias for `path`)
`file_system_file`	Handler-specific file name (alias for `file`)
`file_system_unique_name`	Handler-specific unique name setting
`file_system_create_path`	Handler-specific create path setting
`file_system_check_text`	Handler-specific text check
`file_system_check_image`	Handler-specific image check
`file_system_test_buffer_size`	Handler-specific test buffer size
`file_system_non_printable_threshold`	Handler-specific non-printable threshold

MIME Type Filtering

sql

comment on function fs_upload_include_mime_type(json) is '
upload for file_system
param _meta is upload metadata
path = ./test
file = mime_type.csv
included_mime_types = image/*, application/*
';

CSV Handler

Parses CSV files and processes each row via a PostgreSQL command.

Basic Example

sql

-- Table for uploads
create table csv_upload_table (
    index int,
    id int,
    name text,
    value int,
    prev_result int,
    meta json
);

-- Row command to process each CSV row
create procedure csv_upload_row(
    _index int,
    _row text[]
)
language plpgsql
as
$$
begin
    insert into csv_upload_table (index, id, name, value)
    values (_index, _row[1]::int, _row[2], _row[3]::int);
end;
$$;

-- HTTP POST endpoint
create function csv_simple_upload(
    _meta json = null
)
returns json
language plpgsql
as
$$
begin
    return _meta;
end;
$$;

comment on function csv_simple_upload(json) is '
upload for csv
param _meta is upload metadata
row_command = call csv_upload_row($1,$2)
';

With Previous Result and Metadata

The row command can receive up to 4 parameters:

sql

create function csv_simple_upload_process_row(
    _index int,
    _row text[],
    _prev_result int,
    _meta json
)
returns int
language plpgsql
as
$$
begin
    if _index > 1 then  -- Skip header row
        insert into csv_simple_upload_table (
            index, id, name, value, prev_result, meta
        ) values (
            _index, _row[1]::int, _row[2], _row[3]::int, _prev_result, _meta
        );
    end if;
    return _index;
end;
$$;

comment on function csv_simple_upload(json) is '
upload for csv
param _meta is upload metadata
row_command = select csv_simple_upload_process_row($1,$2,$3,$4)
';

CSV Row Command Parameters

Parameter	Type	Description
`$1`	int	Row index (1-based, includes header)
`$2`	text[]	Parsed row values as text array
`$3`	any	Result of previous row command
`$4`	json	Upload metadata JSON

Custom Delimiters

Support multiple delimiter characters:

sql

comment on function csv_mixed_delimiter_upload(json) is '
upload for csv
param _meta is upload metadata
delimiters = \t,;
row_command = call csv_mixed_delimiter_upload_row($1,$2)
';

This will use tab (\t), comma (,), and semicolon (;) as delimiters.

CSV Annotation Options

All shared options (except buffer_size, check_text, check_image) plus:

Option	Description
`row_command`	PostgreSQL command to process each row
`delimiters`	Delimiter character(s) (default: `,`)
`check_format`	Check file format before processing
`has_fields_enclosed_in_quotes`	Fields may be enclosed in quotes (bool)
`set_white_space_to_null`	Convert whitespace-only values to NULL (bool)
`csv_included_mime_types`	Handler-specific MIME types to include
`csv_excluded_mime_types`	Handler-specific MIME types to exclude
`csv_check_format`	Handler-specific format check
`csv_test_buffer_size`	Handler-specific test buffer size
`csv_non_printable_threshold`	Handler-specific non-printable threshold
`csv_delimiters`	Handler-specific delimiters (alias for `delimiters`)
`csv_has_fields_enclosed_in_quotes`	Handler-specific quotes setting
`csv_set_white_space_to_null`	Handler-specific whitespace setting
`csv_row_command`	Handler-specific row command (alias for `row_command`)

Excel Handler

Parses Excel files and processes each row via a PostgreSQL command.

Basic Example

sql

-- Row command for Excel processing
create procedure excel_upload_row(
    _index int,
    _row text[]
)
language plpgsql
as
$$
begin
    insert into excel_upload_table (index, id, name, value)
    values (_index, _row[1]::int, _row[2], _row[3]::int);
end;
$$;

-- HTTP POST endpoint
create function excel_simple_upload(
    _meta json = null
)
returns json
language plpgsql
as
$$
begin
    return _meta;
end;
$$;

comment on function excel_simple_upload(json) is '
upload for excel
param _meta is upload metadata
row_command = call excel_upload_row($1,$2)
';

Excel Annotation Options

All shared options (except buffer_size, check_text, check_image, test_buffer_size, non_printable_threshold) plus:

Option	Description
`row_command`	PostgreSQL command to process each row
`sheet_name`	Specific sheet name to process
`all_sheets`	Process all sheets in the workbook (bool)
`time_format`	Format for time values (default: `HH:mm:ss`)
`date_format`	Format for date values (default: `yyyy-MM-dd`)
`datetime_format`	Format for datetime values (default: `yyyy-MM-dd HH:mm:ss`)
`row_is_json`	Pass row data as JSON instead of text array (bool)
`excel_included_mime_types`	Handler-specific MIME types to include
`excel_excluded_mime_types`	Handler-specific MIME types to exclude
`excel_sheet_name`	Handler-specific sheet name (alias for `sheet_name`)
`excel_all_sheets`	Handler-specific all sheets setting
`excel_time_format`	Handler-specific time format
`excel_date_format`	Handler-specific date format
`excel_datetime_format`	Handler-specific datetime format
`excel_row_is_json`	Handler-specific row JSON setting
`excel_row_command`	Handler-specific row command (alias for `row_command`)

Error Handling and Rollback

All upload handlers support automatic rollback on error. If the handler function raises an exception, any uploaded data is rolled back:

sql

create function lo_upload_raise_exception(
    _oid bigint,
    _meta json = null
)
returns json
language plpgsql
as
$$
begin
    raise exception 'failed upload';
    return _meta;
end;
$$;

comment on function lo_upload_raise_exception(bigint, json) is '
upload for large_object
param _meta is upload metadata
oid = {_oid}
';

If an exception is raised:

Large Object: The large object is deleted
File System: The uploaded file is deleted
CSV/Excel: All database changes are rolled back

Multiple File Uploads

Upload endpoints support multiple files in a single request. The metadata will be returned as a JSON array with one entry per file.

Behavior

Enables multipart/form-data file uploads
Handlers process the uploaded file (storage, validation, parsing)
Metadata parameter receives file information (name, size, type)
All uploads are transactional - errors trigger rollback
See Upload Options for configuration

Upload configuration - Configure upload handlers and settings
Comment Annotations Guide - How annotations work
Configuration Guide - How configuration works

AUTHORIZE - Protect upload endpoint

UPLOAD ​

Keywords ​

Syntax ​

Handler Types ​

Shared Annotation Options ​

Upload Metadata ​

Large Object Handler ​

Basic Example ​

With Custom OID Parameter ​

Context Metadata ​

Large Object Annotation Options ​

File System Handler ​

Basic Example ​

With Custom Parameters ​

File System Annotation Options ​

MIME Type Filtering ​

CSV Handler ​

Basic Example ​

With Previous Result and Metadata ​

CSV Row Command Parameters ​

Custom Delimiters ​

CSV Annotation Options ​

Excel Handler ​

Basic Example ​

Excel Annotation Options ​

Error Handling and Rollback ​

Multiple File Uploads ​

Behavior ​

Related ​

Related Annotations ​

UPLOAD

Keywords

Syntax

Handler Types

Shared Annotation Options

Upload Metadata

Large Object Handler

Basic Example

With Custom OID Parameter

Context Metadata

Large Object Annotation Options

File System Handler

Basic Example

With Custom Parameters

File System Annotation Options

MIME Type Filtering

CSV Handler

Basic Example

With Previous Result and Metadata

CSV Row Command Parameters

Custom Delimiters

CSV Annotation Options

Excel Handler

Basic Example

Excel Annotation Options

Error Handling and Rollback

Multiple File Uploads

Behavior

Related

Related Annotations