了解从bytea到oid的转换 [英] Understanding cast from bytea to oid

问题描述

我使用 PostgreSQL 9.2 。

Grace Batumbya提供的这个博客条目提供了 bytea 到 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 
);

我不明白为什么我们要创建 bytea_to_lo table？如何使用 PostgreSQL ？

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

推荐答案

不是一个真正的演员。它只是（ab）使用方便的语法。在背景中创建了大对象（LO），该对象单独存储并返回引用它的OID。

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.

每个文档：

所有大对象都存储在一个名为
的系统表中 pg_largeobject 。每个大对象在系统中也有一个条目
表 pg_largeobject_metadata 。可以创建，修改大型对象
，并使用类似于对文件执行标准
操作的读/写API进行删除。

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.

返回的OID基本上是系统表PK的FK pg_largeobject 。

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

CREATE TABLE 完全独立于函数和伪抛出。

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'));

这里会发生什么？

类型 lo 是由附加模块创建的基本类型 oid 下的域 lo （不正确在 Grace Batumbya的blog enty ）。 根据文档：

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:

该模块还提供了一个数据类型 lo ，这实际上只是一个域
的 oid 类型。这对于区分包含大对象引用的数据库列
和那些是其他事物的OID的数据库列非常有用。

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.

decode（） 返回 bytea 。 INSERT 语句将 bytea 值分配给列 largeObj ，它触发一个赋值给它的类型 lo ，这是上面的转型。

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.

现在博客条目草率过时。

The blog entry is sloppy and outdated by now.

• 不必提及（每篇文档） ：

为了能够创建一个转换，您必须拥有源或目标
数据类型，并且具有<$ c

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.

在中键入CREATE TABLE ：列名称和类型颠倒。

Typo in CREATE TABLE: column name and type reversed.

函数定义是冗长和低效的。这将更好（对于Postgres 9.3 或更早）：

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。

内置函数 在Postgres 9.4 中。请改用：

lo_from_bytea(loid oid, string bytea)

从发布说明：

• 添加SQL函数以允许任意偏移量的大对象读取/写入（Pavel Stehule）

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

对于 CREATE CAST （每个文档）：

第一个参数类型必须与转换的源类型相同或二进制强制。

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

只有 bytea 参数的变体：

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;

由于伪类有相当大的副作用，我不相信， code> 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

这篇关于了解从bytea到oid的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！