fixed documentation Regards
Pavel 2013/10/24 Heikki Linnakangas <hlinnakan...@vmware.com> > On 22.10.2013 13:55, Pavel Stehule wrote: > >> 2013/10/21 Noah Misch<n...@leadboat.com> >> >>> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers