Postgresql – PL/pgSQL function + client-side lo_import

plpgsqlpostgresqlpsql

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.

Best Answer

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.

Related Question