using copy in postgresql?

Question

There is a table with 3 columns mydocs(id serial, docform integer, content text)

copy (select (content) from mydocs where id=30 ) to 'D:/html/ex10.xml' ;

I choose 1 row ( id=30 ) with this expression and put (content text) from it in folder with path. It works but

<?xml version="1.0" encoding="utf-8"?>
\r\n
<tutorial>
\r\n
<title>&quot;Заметки об XSL&quot;</title>
\r\n
<author>лермонтов</author>
\r\n
</tutorial>

In folder doc have additional symbols like \r and \n, how to delete them when i copy it, or how to fix an appearance of \r and \n in my file. by the way, this is the way i insert files to database

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;$$;

this is in psql

 insert into mydocs(docform,content)
 values (3, convert_from(bytea_import('D:/html/ex08.xml'), 'utf-8'));

klin · Accepted Answer · 2017-02-27 02:17:32Z

0

It's not quite clear (for me) what exactly the content looks like, however one of these variants should work:

copy (select (replace(content, e'\r\n', '')) from mydocs where id=30 ) to 'c:/data/ex10.xml';
copy (select (replace(content, '\r\n', '')) from mydocs where id=30 ) to 'c:/data/ex10.xml';

edited Feb 27, 2017 at 2:17

answered Feb 26, 2017 at 18:46

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using copy in postgresql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related