Oracle DBMS_LOB.WRITEAPPEND到Postgres转换 [英] Oracle DBMS_LOB.WRITEAPPEND to Postgres Conversion

问题描述

有人可以让我知道如何将下面的Oracle代码转换为Postgresql

Can someone please let me know how to convert the below Oracle Code to Postgresql

IF prodNum = 1 THEN
          DBMS_LOB.WRITEAPPEND(pkgFilterNode, LENGTH(pkgFilter_tab || '<PackageFilters isNewFormat="Y" > '||l_crlf), pkgFilter_tab || '<PackageFilters isNewFormat="Y" > '||l_crlf);
END IF;

感谢您的时间！

推荐答案

这取决于您的大对象的大小.当您的大对象少于500MB时，则不需要使用LOB(PostgreSQL使用术语LO)，并且可以使用text或varchar类型-工作与varchar相似.达到此大小后，您应该使用LO API.

It depends on a size of your large object. When your large objects are less about 500MB, then you don't need to use LOBs (PostgreSQL uses term LO), and you can use a text or varchar type - the work is similar like with varchar. After this size you should to use LO API.

CREATE OR REPLACE FUNCTION writeappend(oid, text)
RETURNS void AS $$
DECLARE
  content bytea;
  fd int;
BEGIN
  content := convert_to($2, getdatabaseencoding());
  fd := lo_open($1, 131072);
  PERFORM lo_lseek(fd, 0, 2);
  IF length(content) <> lowrite(fd, content) THEN
    RAISE EXCEPTION 'not all content was written';
  END IF;
  PERFORM lo_close(fd);
END;
$$ LANGUAGE plpgsql;

postgres=> select lo_creat(-1);
┌──────────┐
│ lo_creat │
╞══════════╡
│    20653 │
└──────────┘
(1 row)

postgres=> select writeappend(20653, e'Hello\r\n');
┌─────────────┐
│ writeappend │
╞═════════════╡
│             │
└─────────────┘
(1 row)

postgres=> select writeappend(20653, e'Hello\r\n');
...

postgres=> select convert_from(lo_get(20653),getdatabaseencoding());
┌──────────────┐
│ convert_from │
╞══════════════╡
│ Hello\r     ↵│
│ Hello\r     ↵│
│              │
└──────────────┘
(1 row)

因此您可以使用LO API，但应首选基本类型.这些类型的限制通常足够好-使用基本类型的操作要舒适得多-有诸如全文本之类的可能性.

So you can use LO API, but basic types should be preferred. The limits for these types is usually good enough - and work with basic types are much more comfortable - with some possibilities like fulltext.

这篇关于Oracle DBMS_LOB.WRITEAPPEND到Postgres转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！