在PostgreSQL中使用副本？ [英] using copy in postgresql?

问题描述

有一个包含3列mydocs（id序列，docform整数，内容文本）的表

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

复制（从mydocs中选择（内容）其中id = 30）到'D：/html/ex10.xml';

我选择1行（id = 30）表达式并将其（内容文本）放入带有路径的文件夹中。
可以，但是

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>

在文件夹doc中有其他符号，如\r和\n，当我将其删除时复制它，或如何修复文件中andr和\n的外观。
，这就是我将文件插入数据库的方式

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

这在psql中

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

推荐答案

（对我而言）目前尚不清楚内容看起来像，但是这些变体之一应该起作用：

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

这篇关于在PostgreSQL中使用副本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！