fixed documentation
Regards
Pavel
2013/10/24 Heikki Linnakangas <[email protected]>
> On 22.10.2013 13:55, Pavel Stehule wrote:
>
>> 2013/10/21 Noah Misch<[email protected]>
>>
>>> If you're prepared to change the function names and add the
>>> subset-oriented
>>> functions, I would appreciate that.
>>>
>>> here is patch
>>
>
> lobj.sgml still refer to the old names.
>
> - Heikki
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e397386..8509d09 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3399,6 +3399,78 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
See also the aggregate function <function>string_agg</function> in
<xref linkend="functions-aggregate">.
</para>
+
+ <table id="functions-binarystring-largeobjects-transformations">
+ <title>Loading and saving from/to Large Objects Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_create</primary>
+ </indexterm>
+ <literal><function>lo_create(<parameter>loid</parameter> <type>oid</type>, <parameter>string</parameter> <type>bytea</type> )</function></literal>
+ </entry>
+ <entry><type>oid</type></entry>
+ <entry>
+ Create a large object and store a binary string there. Returns a oid of
+ created large object.
+ </entry>
+ <entry><literal>select lo_create(0, decode('ffffff00','hex'))</literal></entry>
+ <entry><literal>24528</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_get</primary>
+ </indexterm>
+ <literal><function>lo_get(<parameter>loid</parameter> <type>oid</type> <optional>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type></optional>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Returns a binary string based on content a entered large object. Attention: binary
+ string has lower size limit (1GB) than large objects (4GB). Processing very large
+ large object can be very expensive for memory resources. Bytea data are completly
+ holded in memomory.
+ </entry>
+ <entry><literal>lo_get(24628)</literal></entry>
+ <entry><literal>\xffffff00</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_put</primary>
+ </indexterm>
+ <literal><function>lo_put(<parameter>loid</parameter><type>oid</type>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Write data at offset.
+ </entry>
+ <entry><literal>lo_put(24628, 0, decode('aaaa', 'hex'))</literal></entry>
+ <entry></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ See also a description of other Large Objects Function
+ in <xref linkend="lo-funcs">.
+ </para>
</sect1>
diff --git a/doc/src/sgml/lobj.sgml b/doc/src/sgml/lobj.sgml
index bb3e08f..32fda11 100644
--- a/doc/src/sgml/lobj.sgml
+++ b/doc/src/sgml/lobj.sgml
@@ -580,6 +580,42 @@ SELECT lo_export(image.raster, '/tmp/motd') FROM image
these functions as <function>loread</> and <function>lowrite</>.
</para>
+ <para>
+ There are other two functions , that doesn't correspond with client api
+ (see in <xref linkend="functions-binarystring-largeobjects-transformations">).
+ <function>lo_create</function> transforms a binary string to lo object,
+ <function>lo_get</function> transforms a lo object to binary string, and
+ <function>lo_put</function> write a binary string at offset to lo object.
+ </para>
+
+ <para>
+ Some examples:
+<programlisting>
+SELECT lo_create(0, decode('ffffff00','hex'));
+ lo_create
+-----------
+ 16392
+(1 row)
+
+SELECT lo_get(16392);
+ lo_get
+------------
+ \xffffff00
+(1 row)
+
+SELECT lo_put(16392, 1, decode('aa','hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(16392);
+ lo_get
+------------
+ \xffaaff00
+(1 row)
+</programlisting>
+ </para>
</sect1>
<sect1 id="lo-examplesect">
diff --git a/src/backend/libpq/be-fsstubs.c b/src/backend/libpq/be-fsstubs.c
index fa00383..aa12349 100644
--- a/src/backend/libpq/be-fsstubs.c
+++ b/src/backend/libpq/be-fsstubs.c
@@ -754,3 +754,137 @@ deleteLOfd(int fd)
{
cookies[fd] = NULL;
}
+
+/*****************************************************************************
+ * auxiliary LO functions for management LO from SQL and PL
+ *****************************************************************************/
+
+/*
+ * Load LO fragment and returns bytea
+ *
+ * When nbytes is a -1, then it reads from start (specified by offset) to end.
+ */
+static bytea *
+lo_get_fragment_internal(Oid loOid, int64 offset, int nbytes)
+{
+ LargeObjectDesc *loDesc;
+ int64 loSize;
+ int result_length;
+ int total_read;
+ bytea *result = NULL;
+
+ /*
+ * We don't actually need to store into fscxt, but create it anyway to
+ * ensure that AtEOXact_LargeObject knows there is state to clean up
+ */
+ CreateFSContext();
+
+ loDesc = inv_open(loOid, INV_READ, fscxt);
+
+ /* Permission check */
+ if (!lo_compat_privileges &&
+ pg_largeobject_aclcheck_snapshot(loDesc->id,
+ GetUserId(),
+ ACL_SELECT,
+ loDesc->snapshot) != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for large object %u",
+ loDesc->id)));
+
+ loSize = inv_seek(loDesc, 0, SEEK_END);
+
+ if (loSize > offset)
+ {
+ if (nbytes >= 0 && offset + nbytes <= loSize)
+ result_length = nbytes;
+ else
+ result_length = loSize - offset;
+ }
+ else
+ result_length = 0;
+
+ result = (bytea *) palloc(VARHDRSZ + result_length);
+
+ inv_seek(loDesc, offset, SEEK_SET);
+ total_read = inv_read(loDesc, VARDATA(result), result_length);
+
+ Assert(total_read == result_length);
+
+ inv_close(loDesc);
+
+ SET_VARSIZE(result, result_length + VARHDRSZ);
+
+ return result;
+}
+
+/*
+ * Get LO as bytea
+ */
+Datum
+lo_get(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ bytea *result;
+
+ result = lo_get_fragment_internal(loOid, 0, -1);
+
+ PG_RETURN_BYTEA_P(result);
+}
+
+Datum
+lo_get_fragment(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ int32 nbytes = PG_GETARG_INT32(2);
+
+ bytea *result;
+
+ result = lo_get_fragment_internal(loOid, offset, nbytes);
+
+ PG_RETURN_BYTEA_P(result);
+}
+
+/*
+ * Create LO from bytea
+ */
+Datum
+lo_create_bytea(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ bytea *str = PG_GETARG_BYTEA_PP(1);
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loOid = inv_create(loOid);
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_OID(loOid);
+}
+
+/*
+ * Modify LO object
+ */
+Datum
+lo_put(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ bytea *str = PG_GETARG_BYTEA_PP(2);
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_seek(loDesc, offset, SEEK_SET);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_VOID();
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 08586ae..ca4fc62 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1055,6 +1055,15 @@ DESCR("truncate large object");
DATA(insert OID = 3172 ( lo_truncate64 PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 23 "23 20" _null_ _null_ _null_ _null_ lo_truncate64 _null_ _null_ _null_ ));
DESCR("truncate large object (64 bit)");
+DATA(insert OID = 3457 ( lo_create PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 26 "26 17" _null_ _null_ _null_ _null_ lo_create_bytea _null_ _null_ _null_ ));
+DESCR("create new large object with content");
+DATA(insert OID = 3458 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 17 "26" _null_ _null_ _null_ _null_ lo_get _null_ _null_ _null_ ));
+DESCR("read entire large object");
+DATA(insert OID = 3459 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 17 "26 20 23" _null_ _null_ _null_ _null_ lo_get_fragment _null_ _null_ _null_ ));
+DESCR("read large object from offset for length");
+DATA(insert OID = 3460 ( lo_put PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 2278 "26 20 17" _null_ _null_ _null_ _null_ lo_put _null_ _null_ _null_ ));
+DESCR("write data at offset");
+
DATA(insert OID = 959 ( on_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "600 628" _null_ _null_ _null_ _null_ on_pl _null_ _null_ _null_ ));
DATA(insert OID = 960 ( on_sl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "601 628" _null_ _null_ _null_ _null_ on_sl _null_ _null_ _null_ ));
DATA(insert OID = 961 ( close_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 600 "600 628" _null_ _null_ _null_ _null_ close_pl _null_ _null_ _null_ ));
diff --git a/src/include/libpq/be-fsstubs.h b/src/include/libpq/be-fsstubs.h
index a2b803a..50b9190 100644
--- a/src/include/libpq/be-fsstubs.h
+++ b/src/include/libpq/be-fsstubs.h
@@ -25,6 +25,7 @@ extern Datum lo_export(PG_FUNCTION_ARGS);
extern Datum lo_creat(PG_FUNCTION_ARGS);
extern Datum lo_create(PG_FUNCTION_ARGS);
+extern Datum lo_create_bytea(PG_FUNCTION_ARGS);
extern Datum lo_open(PG_FUNCTION_ARGS);
extern Datum lo_close(PG_FUNCTION_ARGS);
@@ -32,6 +33,10 @@ extern Datum lo_close(PG_FUNCTION_ARGS);
extern Datum loread(PG_FUNCTION_ARGS);
extern Datum lowrite(PG_FUNCTION_ARGS);
+extern Datum lo_get(PG_FUNCTION_ARGS);
+extern Datum lo_get_fragment(PG_FUNCTION_ARGS);
+extern Datum lo_put(PG_FUNCTION_ARGS);
+
extern Datum lo_lseek(PG_FUNCTION_ARGS);
extern Datum lo_tell(PG_FUNCTION_ARGS);
extern Datum lo_lseek64(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/input/largeobject.source b/src/test/regress/input/largeobject.source
index f0ea7a2..944b623 100644
--- a/src/test/regress/input/largeobject.source
+++ b/src/test/regress/input/largeobject.source
@@ -203,5 +203,22 @@ SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
SELECT lo_unlink(loid) FROM lotest_stash_values;
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+
+\set newloid_1 :LASTOID
+
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+
+SELECT lo_get(:newloid_1, 0, 20);
+SELECT lo_get(:newloid_1, 10, 20);
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+SELECT lo_get(:newloid_1, 0, 20);
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
+
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject.source b/src/test/regress/output/largeobject.source
index a25ac2a..9bdf75c 100644
--- a/src/test/regress/output/largeobject.source
+++ b/src/test/regress/output/largeobject.source
@@ -391,5 +391,41 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject_1.source b/src/test/regress/output/largeobject_1.source
index bae74f6..b02489f 100644
--- a/src/test/regress/output/largeobject_1.source
+++ b/src/test/regress/output/largeobject_1.source
@@ -391,5 +391,41 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
