PostgreSQL \lo_import 以及如何在UPDATE命令中获取结果的OID？[英] PostgreSQL \lo_import and how to get the resulting OID into an UPDATE command?

问题描述

I'm working with Postgres 9.0, and I have an application where I need to insert images into the remote server. So I use:

 "C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.12 -p 5432 -d myDB -U my_admin -c  "\lo_import 'C://im/zzz4.jpg'";

where

192.168.1.12 is the IP address of the server system

5432 is the Port number

myDB is server database name

my_admin is the username

"\lo_import 'C://im/zzz4.jpg'" is the query that is fired.

After the image has been inserted into the database I need to update a row in a table like this:

UPDATE species 
SET    speciesimages=17755;  -- OID from previous command.. how to get the OID ??
WHERE  species='ACCOAA';

So my question is: how do I get the OID returned after the \lo_import in psql?

I tried running \lo_import 'C://im/zzz4.jpg' in Postgres but I get an error:

ERROR:  syntax error at or near ""\lo_import 'C://im/zzz4.jpg'""
LINE 1: "\lo_import 'C://im/zzz4.jpg'"

I also tried this:

update species
set speciesimages=\lo_import 'C://im/zzz4.jpg'
where species='ACAAC04';

But I get this error:

ERROR:  syntax error at or near "\"
LINE 2: set speciesimages=\lo_import 'C://im/zzz4.jpg'
                          ^

推荐答案

As your file resides on your local machine and you want to import the blob to a remote server, you have two options:

1) Transfer the file to the server and use the server-side function:

UPDATE species
SET    speciesimages = lo_import('/path/to/server-local/file/zzz4.jpg')
WHERE  species = 'ACAAC04';

2) Use the psql meta-command like you have it.

But you cannot mix psql meta commands with SQL-commands, that's impossible.
Use the psql variable :LASTOID in an UPDATE command that you launch immediately after the \lo_import meta command in the same psql session:

UPDATE species
SET    speciesimages = :LASTOID
WHERE  species = 'ACAAC04';

To script that (works in Linux, I am not familiar with Windows shell scripting):

echo "\lo_import '/path/to/my/file/zzz4.jpg' \\\\ UPDATE species SET speciesimages = :LASTOID WHERE  species = 'ACAAC04';" | \
psql -h 192.168.1.12 -p 5432 -d myDB -U my_admin

• \\ is the separator meta-command. You need to double the \, in a "" string, because the shell interprets one layer.
• \ before the newline is just the line continuation in Linux shells.

Alternative syntax (tested on Linux again):

psql -h 192.168.1.12 -p 5432 -d myDB -U my_admin << EOF
\lo_import '/path/to/my/file/zzz4.jpg'
UPDATE species
SET    speciesimages = :LASTOID
WHERE  species = 'ACAAC04';
EOF

其他推荐答案

After importing an image with this command:

\lo_import '$imagePath' '$imageName'

You can then find the description of the binary by querying the pg_catalog.pg_largeobject_metadata table which stores the oid value you need.

Ie:

"SELECT oid as `"ID`",
pg_catalog.obj_description(oid, 'pg_largeobject') as `"Description`"
FROM pg_catalog.pg_largeobject_metadata WHERE pg_catalog.obj_description(oid,'pg_largeobject') = '$image' limit 1 "

其他推荐答案

Here's how to do it if your field is type bytea.

\lo_import '/cygdrive/c/Users/Chloe/Downloads/Contract.pdf'
update contracts set contract = lo_get(:LASTOID) where id = 77;

Use \lo_list and \lo_unlink after you import.