在服务器系统上的postgreSQL中将\COPY与BINARY一起用于lo_export [英] using \COPY with BINARY for lo_export in postgreSQL on server system

问题描述

我正在使用PostgreSQL 9进行应用程序的开发，我有一个数据库，其中包含一个物种表
，其中存储了鱼类的详细信息以及该物种的图像。
表是

i am working wit h PostgreSQL 9 for an application, i have a database with a table 'species' where i store fish species details along with the image of the species. the table is

       CREATE TABLE fishes
               (
                  fishes character varying(7) NOT NULL,
                  speciesimages oid,
                  CONSTRAINT species_pkey PRIMARY KEY (species)
                 )
                   WITH (
                       OIDS=TRUE
               );

我使用

INSERT INTO species(fishes,fishesimages VALUES('01',lo_import('C://01.jpg'));

将图像存储在数据库中。

To store the images in the database.

检索我使用的图像

       SELECT lo_export(fishes.fishesimages,'c://outimage.jpg') 
       FROM   fishes
       WHERE  fishes= '01'; 

当主机为 Localhost 时，这很好用，但是当它是服务器时，我不能使用路径 c：// ，因为该路径可能在服务器系统上不存在，而且我也没有权限。

This works fine when the host is Localhost but when it is the server i cannot use the path c:// as this may not exist on the server system and i dont have permissions anyways.

所以我开始使用

\复制

/ p>

like this

"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d myDB -U DB_admin -c  "\COPY (SELECT lo_export(fishes.fishesimages,'01.jpg') FROM   fishes WHERE  species = '01') TO 'C://leeImage.jpeg' WITH BINARY";

但这会创建一个图像文件，但是当我打开它的无效图像时

but this create a image file but when i open it its invalid image

可以有人告诉我如何使用服务器计算机上的 lo_export 函数并在客户端计算机上创建映像吗？

can anyone tell me how to use lo_export function from the server machine and create the image on client machine?

推荐答案

基本上：

1. lo_export 将指示服务器在本地（始终）写入文件


2. \copy 将由psql转换为 COPY。 .. TO STDOUT 命令并将输出写入指定的文件。 （因此，写入该文件的是您之前执行的 select 陈述的结果）

1. lo_export will instruct the server to write a file locally (always)
2. \copy will be transformed by psql to a COPY ... TO STDOUT command and the output written to the specified file. (So what is written to that file is the result of the select statment you were doing before)

因此，您不能不以这种方式使用 lo_export ，它将始终写入文件

So, you can not use lo_export in this way, it will always write a file onto the server's filesystem.

当然，您可以通过将服务器写入共享驱动器，然后从该驱动器读取文件来解决此问题。

Of course, you can solve this simply by having the server write to a shared drive, and then read the file from that drive. Ugly, but effective IMHO.

对于某些最新版本的psql（不确定何时引入），有一个 \lo_export psql命令，它带有OID和文件名，例如：

For some recent versions of psql (not sure when this was introduced) there is a \lo_export psql command which takes an OID and filename, e.g.:

\lo_export 28914648 testfile

但是您需要以某种方式将文件的OID放入脚本中...

However you need to get the OID of the file into the script somehow...

您可以编写如下的PL / PGsql函数以将文件作为字节转储：

You can write a PL/PGsql function like this to dump the file as a bytea:

CREATE OR REPLACE FUNCTION getfiledata(lobjid oid) RETURNS bytea
  STRICT STABLE LANGUAGE plpgsql AS $$
DECLARE
  fd int4;
  imgsize int4;
  INV_READ int4 := 262144;
  SEEK_SET int4 := 0;
  SEEK_END int4 := 2;
BEGIN
  SELECT lo_open(lobjid, INV_READ) INTO fd;
  PERFORM lo_lseek(fd, 0, SEEK_END);
  SELECT lo_tell(fd) INTO imgsize;
  PERFORM lo_lseek(fd, 0, SEEK_SET);
  RETURN loread(fd, imgsize);
END;
$$;

现在使用大对象的OID调用此函数将返回其内容作为字节值。因此，您可以在COPY命令中调用此函数，它将返回文件数据...，并使用 \copy 将其发送到客户端。

Now calling this function with the OID of the large object will return its content as a bytea value. You can thus call this function in a COPY command and it will return the file data... and by using \copy it will be sent to the client.

这些天通常建议直接使用 bytea 列，而不要使用此大对象接口（bytea稍后引入）。 PostgreSQL会自动将较大的值移到离线存储（ TOAST表）中并进行压缩（除非将存储模式设置为外部以抑制这种情况，这对于JPEG图像可能是正确的做法）等等）

It's generally recommended these days to use bytea columns directly rather than this large object interface (bytea was introduced a lot later). PostgreSQL will automatically move large values into out-of-line storage ("TOAST tables") and will also compress them (unless the storage mode is set to "external" to suppress this, which is probably the right thing to do for JPEG images etc)

编辑：
\lo_export
在 commandprompt.com

 `"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d myDB -U DB_admin -c  "\lo_export 19135 'C://leeImage.jpeg' ";`

其中数字 19135 是我要在客户端系统上获取其图像的物种的OID。您可以从 fishes 表 fishesimages OID
使用上面的代码中的OID，您可以使用OID获取图像。

where the number 19135 is the OID of the species whose image i want on the client system.. the OID you can get from the fishes table fishesimages OID use the OID in the above code and you can use the OID get the images.

这篇关于在服务器系统上的postgreSQL中将\COPY与BINARY一起用于lo_export的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！