Large OBject (LOB) types

IBM^® Informix^® and Genero support the TEXT and BYTE types to store large objects: TEXT is used to store large text data, while BYTE is used to store large binary data like images or sound.

PostgreSQL provides the TEXT and BYTEA data types for large objects storage. With these data types, large objects are handled as a whole. In fact PostgreSQL does also provide another way to store blobs, through the large objects facility based on stream-style access. The large object facility is provided as a set of C and SQL API functions to create / delete / modify large objects identified by a unique object id (OID). For example, the lo_create(-1) SQL function will create a new large object and return a new object id that will be used to handle the LOB. See PostgreSQL documentation for more details.

Solution

TEXT and BYTE data can be stored in PostgreSQL TEXT and BYTEA columns.

Genero BDL does not interface automatically with the PostgreSQL Large Object facility. However, the OID values can be stored in BIGINT variables, and you can use server-side LOB functions to convert large objects to BYTEA data, that can be fetched into BYTE variables. The next code example creates a table with an OID column, imports a LOB from an image file, and then fetches the LOB back into a BYTE:

MAIN
    DEFINE img BYTE, obj_id BIGINT

    CONNECT TO "test1+driver='dbmpgs'" USER "postgres" USING "fourjs"

    # Need superuser privileges to create the LOB....
    WHENEVER ERROR CONTINUE
    DROP TABLE t1
    WHENEVER ERROR STOP
    EXECUTE IMMEDIATE "create table t1 ( k int, image oid )"
    GRANT SELECT ON t1 TO PUBLIC
    INSERT INTO t1 VALUES ( 1, lo_import("/var/images/landscape.png") )
    SELECT image INTO obj_id FROM t1 WHERE k=1
    DISPLAY "obj_id = ", obj_id
    EXECUTE IMMEDIATE "grant select on large object "||obj_id||" to public"

    # Next block can be executed by any user:
    LOCATE img IN FILE -- a temp file will be used
    SELECT loread(lo_open(image, 262144), 1000000)
      INTO img FROM t1 WHERE k=1
    DISPLAY length(img)

    # Delete the object
    SELECT lo_unlink(obj_id) FROM t1 WHERE k=1

    DROP TABLE t1

END MAIN