Understanding cast from bytea to oid

Question

I'm using PostgreSQL 9.2.
This blog entry by Grace Batumbya provides a cast from bytea to oid.

create or replace function blob_write(lbytea bytea)
   returns oid
   volatile
   language plpgsql as
$f$
   declare
      loid oid;
      lfd integer;
      lsize integer;
begin
   if(lbytea is null) then
      return null;
   end if;

   loid := lo_create(0);
   lfd := lo_open(loid,131072);
   lsize := lowrite(lfd,lbytea);
   perform lo_close(lfd);
   return loid;
end;
$f$;
CREATE CAST (bytea AS oid) WITH FUNCTION blob_write(bytea) AS ASSIGNMENT;

CREATE TABLE bytea_to_lo (
   largeObj lo 
);

I didn't understand why should we create bytea_to_lo table? How is it going to be used by PostgreSQL?

@ErwinBrandstetter Sure, I got that from [there](https://gbatumbya.wordpress.com/2011/06/15/create-a-large-object-lo-from-binary-data-bytea-on-server-side/). — user3663882, Apr 03 '15 at 15:07
Related: http://stackoverflow.com/questions/10139077/convert-a-bytea-column-to-oid-column-with-retained-values-postgresql — Erwin Brandstetter, Apr 03 '15 at 16:48
This is a pretty bad idea IMO, and should be written with an explicit function call instead. — Craig Ringer, Apr 04 '15 at 08:32

score 1 · Accepted Answer · edited May 23 '17 at 11:43

The cast is not a true cast. It's just (ab)using the convenient syntax. A large object (LO) is created in the background which is stored separately and the OID referencing it is returned.

Per documentation:

All large objects are stored in a single system table named pg_largeobject. Each large object also has an entry in the system table pg_largeobject_metadata. Large objects can be created, modified, and deleted using a read/write API that is similar to standard operations on files.

The returned OID is basically a FK to the PK of the system table pg_largeobject.

CREATE TABLE is completely independent from the function and pseudo-cast.

CREATE TABLE bytea_to_lo (
   largeObj lo 
);

It's just a typical use case for the assignment cast created above, which becomes apparent from the following line that you forgot to quote:

INSERT INTO bytea_to_lo VALUES (DECODE('00AB','hex'));

What happens here?

The data type lo is a domain over the base type oid, created by the additional module lo (incorrectly referenced as "lo_manage package" in the blog enty of Grace Batumbya). Per documentation:

The module also provides a data type lo, which is really just a domain of the oid type. This is useful for differentiating database columns that hold large object references from those that are OIDs of other things.

The function decode() returns bytea. The INSERT statement assigns the bytea value to the column largeObj, which triggers an assignment cast to its type lo, and that's where the above cast comes in.

Warning / Corrective / Update

The blog entry is sloppy and outdated by now.

Does not bother to mention that (per documentation):

To be able to create a cast, you must own the source or the target data type and have USAGE privilege on the other type.

Effectively, you must be superuser.
Typo in CREATE TABLE: column name and type reversed.

The function definition is verbose and inefficient. This would be better (for Postgres 9.3 or older):

CREATE OR REPLACE FUNCTION blob_write(bytea)
  RETURNS oid AS
$func$
DECLARE
   loid oid := lo_create(0);
   lfd  int := lo_open(loid,131072);  -- = 2^17 = x2000
   -- symbolic constant defined in the header file libpq/libpq-fs.h
   -- #define   INV_WRITE   0x00020000
BEGIN
   PERFORM lowrite(lfd, $1);
   PERFORM lo_close(lfd);
   RETURN loid;
END
$func$  LANGUAGE plpgsql VOLATILE STRICT;

SQL Fiddle.

There is a built-in function for this in Postgres 9.4. Use that instead:

lo_from_bytea(loid oid, string bytea)

From the release notes:

Add SQL functions to allow large object reads/writes at arbitrary offsets (Pavel Stehule)

For CREATE CAST (per documentation):

The first argument type must be identical to or binary-coercible from the cast's source type.

I suggest an overloaded variant with only a bytea parameter:

CREATE OR REPLACE FUNCTION lo_from_bytea(bytea)
   RETURNS oid LANGUAGE sql AS
'SELECT lo_from_bytea(0, $1)';

CREATE CAST (bytea AS oid) WITH FUNCTION lo_from_bytea(bytea) AS ASSIGNMENT;

Since the pseudo-cast has quite a big side effect, I am not convinced to make that an ASSIGNMENT cast. I'd probably start with explicit-only:

Generate series of dates - using date type as input

Understanding cast from bytea to oid

1 Answers1

What happens here?

Warning / Corrective / Update

Linked