CODEWITHSUNDEEP
postgresqlpdfremote-accessinsert-update
Avinash Polisetty
Avinash Polisetty

Reputation: 11

How to insert pdf into postgresql(remote db)

I have a pdffile.

I could insert the pdf file and db is available in same server using below function and command.

function

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

command:

 update jicontentresource set  data = bytea_import('/root/Desktop/Avinash/Avinash.pdf')
 where id =245

When file is in local server and, db is available in another server (remote)

If i try to do for remote server, using below command

update jicontentresource set  data = bytea_import('/root/Desktop/Avinash/Avinash.pdf') where id =245

it is not able to take file from local.

is there a way to achieve this?

Upvotes: 0

Views: 3571

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51496

you can just separate local and remote stuff:

create or replace function bytea_import(l_oid int, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then run on client side:

t=# select lo_import('/tmp/build.start');
 lo_import
-----------
 447349949
(1 row)
 update jicontentresource set  data = bytea_import(447349949)
 where id =245

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246568

You will not be able to do that with a PostgreSQL function, because that function runs on the database server, while the file is on the file system on the client side.

You'll have to write code in a client side programming language and read and import the file this way.

Depending on the API you are using, you could read the file into memory and use INSERTor UPDATE, or you could use the COPY ... FROM STDIN SQL command.

Upvotes: 2

Related Questions