Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this:

create function add_file(flag integer, sth varchar) returns void as
begin
   if flag = 1 then 
      insert into tab_one values (my_file_oid, sth);
   else
      insert into tab_two values (my_file_oid, sth);
   end if;
end;

I'm using the psql command:

\lo_import('path/to/file');

Both the function and the lo_import command are in the same .sql file.

I cant change the lo_import() to an INSERT statement, becouse I need client-site lo_import. There is variable LASTOID, but it is not avaible in the add_file function, and it wouldnt get updated updating on every call add_file().

So, how can I insert a large object into the database with, in our example, 'flag' and 'sth' by insert statement and everything in function with arguments?

The file to be loaded is on the client computer.

share|improve this question
    
Any luck with this? –  Craig Ringer Aug 27 '12 at 1:19

1 Answer 1

up vote 1 down vote accepted

psql is the wrong tool for this kind of job because it basically can't get results into variables. It's good for user interface and static scripts, but for interactive scripting where results from one operation are to be piped into another, it falls short. The best solution is to switch to a real scripting language such as Perl or Python that have good client-side APIs, including large-objects streaming.

When limited to bash, it's still possible to operate psql from the outside with coproc to get results out and back in, but it's much more complicated.

share|improve this answer
    
I wrote about the coproc approach in this answer, but like Daniel I'd really recommend using a language with native postgresql support - Python, Java, Perl, etc. Many of those languages' PostgreSQL client drivers support client-side import of large objects. –  Craig Ringer Aug 27 '12 at 1:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.