BLOB_APPEND()

Procedural SQL (PSQL) StatementsAggregate Functions

`BLOB_APPEND()`

Efficient concatenation of blobs

Result type

BLOB

Syntax

BLOB_APPEND(expr1, expr2 [, exprN ... ])

Table 1. `BLOB_APPEND` Function Parameters
Parameter	Description
exprN	An expression of a type convertible to `BLOB`

The BLOB_APPEND function concatenates blobs without creating intermediate BLOBs, avoiding excessive memory consumption and growth of the database file.The BLOB_APPEND function takes two or more arguments and adds them to a BLOB which remains open for further modification by a subsequent BLOB_APPEND call.

The resulting BLOB is left open for writing instead of being closed when the function returns.In other words, the BLOB can be appended as many times as required.The engine marks the BLOB returned by BLOB_APPEND with an internal flag, BLB_close_on_read, and closes it automatically when needed.

The first argument determines the behaviour of the function:

NULL: new, empty BLOB SUB_TYPE TEXT is created, using the connection character set as the character set
permanent BLOB (from a table) or temporary BLOB which was already closed: new BLOB is created with the same subtype and, if subtype is TEXT the same character set, populated with the content of the original BLOB.
temporary unclosed BLOB with the BLB_close_on_read flag (e.g. created by another call to BLOB_APPEND): used as-is, remaining arguments are appended to this BLOB
other data types: a new BLOB SUB_TYPE TEXT is created, populated with the original argument converted to string.If the original value is a character type, its character set is used (for string literals, the connection character set), otherwise the connection character set.

Other arguments can be of any type.The following behavior is defined for them:

NULLs are ignored (behaves as empty string)
BLOBs, if necessary, are transliterated to the character set of the first argument and their contents are appended to the result
other data types are converted to strings (as usual) and appended to the result

The BLOB_APPEND function returns a temporary unclosed BLOB with the BLB_close_on_read flag.If the first argument is such a temporary unclosed BLOB (e.g. created by a previous call to BLOB_APPEND), it will be used as-is, otherwise a new BLOB is created.Thus, a series of operations like blob = BLOB_APPEND (blob, …) will result in the creation of at most one BLOB (unless you try to append a BLOB to itself).This blob will be automatically closed by the engine when the client reads it, assigns it to a table, or uses it in other expressions that require reading the content.

Warning

Important caveats for BLOB_APPEND

The NULL behaviour of BLOB_APPEND is different from normal concatenation (using ||).Occurrence of NULL will behave as if an empty string was used.In other words, NULL is effectively ignored.

In normal concatenation, concatenating with NULL results in NULL.

Note	Testing a blob for `NULL` using the `IS [NOT] NULL` operator does not read it and therefore a temporary blob with the `BLB_close_on_read` flag will not be closed after such a test.

Tip	Use `LIST` or `BLOB_APPEND` functions to concatenate blobs.This reduces memory consumption and disk I/O, and also prevents database growth due to the creation of many temporary blobs when using the concatenation operator.

`BLOB_APPEND` Examples

execute block
returns (b blob sub_type text)
as
begin
  -- creates a new temporary not closed BLOB
  -- and writes the string from the 2nd argument into it
  b = blob_append(null, 'Hello ');

  -- adds two strings to the temporary BLOB without closing it
  b = blob_append(b, 'World', '!');

  -- comparing a BLOB with a string will close it, because the BLOB needs to be read
  if (b = 'Hello World!') then
  begin
  -- ...
  end

  -- creates a temporary closed BLOB by adding a string to it
  b = b || 'Close';

  suspend;
end

BLOB_APPEND()

BLOB_APPEND()

BLOB_APPEND Examples

`BLOB_APPEND()`

`BLOB_APPEND` Examples