Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc50b7c0

Browse files
committed
Add large object functions catering to SQL callers.
With these, one need no longer manipulate large object descriptors andextract numeric constants from header files in order to read and writelarge object contents from SQL.Pavel Stehule, reviewed by Rushabh Lathia.
1 parent9c339eb commitc50b7c0

File tree

9 files changed

+362
-7
lines changed

9 files changed

+362
-7
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3420,7 +3420,8 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
34203420

34213421
<para>
34223422
See also the aggregate function <function>string_agg</function> in
3423-
<xref linkend="functions-aggregate">.
3423+
<xref linkend="functions-aggregate"> and the large object functions
3424+
in <xref linkend="lo-funcs">.
34243425
</para>
34253426
</sect1>
34263427

‎doc/src/sgml/lobj.sgml

Lines changed: 73 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -526,11 +526,79 @@ int lo_unlink(PGconn *conn, Oid lobjId);
526526
<title>Server-side Functions</title>
527527

528528
<para>
529-
There are server-side functions callable from SQL that correspond to
530-
each of the client-side functions described above; indeed, for the
531-
most part the client-side functions are simply interfaces to the
532-
equivalent server-side functions. The ones that are actually useful
533-
to call via SQL commands are
529+
Server-side functions tailored for manipulating large objects from SQL are
530+
listed in <xref linkend="lo-funcs-table">.
531+
</para>
532+
533+
<table id="lo-funcs-table">
534+
<title>SQL-oriented Large Object Functions</title>
535+
<tgroup cols="5">
536+
<thead>
537+
<row>
538+
<entry>Function</entry>
539+
<entry>Return Type</entry>
540+
<entry>Description</entry>
541+
<entry>Example</entry>
542+
<entry>Result</entry>
543+
</row>
544+
</thead>
545+
546+
<tbody>
547+
<row>
548+
<entry>
549+
<indexterm>
550+
<primary>lo_create</primary>
551+
</indexterm>
552+
<literal><function>lo_create(<parameter>loid</parameter> <type>oid</type>, <parameter>string</parameter> <type>bytea</type>)</function></literal>
553+
</entry>
554+
<entry><type>oid</type></entry>
555+
<entry>
556+
Create a large object and store data there, returning its OID.
557+
Pass <literal>0</> to have the system choose an OID.
558+
</entry>
559+
<entry><literal>lo_create(0, E'\\xffffff00')</literal></entry>
560+
<entry><literal>24528</literal></entry>
561+
</row>
562+
563+
<row>
564+
<entry>
565+
<indexterm>
566+
<primary>lo_put</primary>
567+
</indexterm>
568+
<literal><function>lo_put(<parameter>loid</parameter> <type>oid</type>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type>)</function></literal>
569+
</entry>
570+
<entry><type>void</type></entry>
571+
<entry>
572+
Write data at the given offset.
573+
</entry>
574+
<entry><literal>lo_put(24528, 1, E'\\xaa')</literal></entry>
575+
<entry></entry>
576+
</row>
577+
578+
<row>
579+
<entry>
580+
<indexterm>
581+
<primary>lo_get</primary>
582+
</indexterm>
583+
<literal><function>lo_get(<parameter>loid</parameter> <type>oid</type> <optional>, <parameter>from</parameter> <type>bigint</type>, <parameter>for</parameter> <type>int</type></optional>)</function></literal>
584+
</entry>
585+
<entry><type>bytea</type></entry>
586+
<entry>
587+
Extract contents or a substring thereof.
588+
</entry>
589+
<entry><literal>lo_get(24528, 0, 3)</literal></entry>
590+
<entry><literal>\xffaaff</literal></entry>
591+
</row>
592+
593+
</tbody>
594+
</tgroup>
595+
</table>
596+
597+
<para>
598+
There are additional server-side functions corresponding to each of the
599+
client-side functions described earlier; indeed, for the most part the
600+
client-side functions are simply interfaces to the equivalent server-side
601+
functions. The ones just as convenient to call via SQL commands are
534602
<function>lo_creat</function><indexterm><primary>lo_creat</></>,
535603
<function>lo_create</function><indexterm><primary>lo_create</></>,
536604
<function>lo_unlink</function><indexterm><primary>lo_unlink</></>,

‎src/backend/libpq/be-fsstubs.c

Lines changed: 151 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -754,3 +754,154 @@ deleteLOfd(int fd)
754754
{
755755
cookies[fd]=NULL;
756756
}
757+
758+
/*****************************************************************************
759+
*Wrappers oriented toward SQL callers
760+
*****************************************************************************/
761+
762+
/*
763+
* Read [offset, offset+nbytes) within LO; when nbytes is -1, read to end.
764+
*/
765+
staticbytea*
766+
lo_get_fragment_internal(OidloOid,int64offset,int32nbytes)
767+
{
768+
LargeObjectDesc*loDesc;
769+
int64loSize;
770+
int64result_length;
771+
inttotal_readPG_USED_FOR_ASSERTS_ONLY;
772+
bytea*result=NULL;
773+
774+
/*
775+
* We don't actually need to store into fscxt, but create it anyway to
776+
* ensure that AtEOXact_LargeObject knows there is state to clean up
777+
*/
778+
CreateFSContext();
779+
780+
loDesc=inv_open(loOid,INV_READ,fscxt);
781+
782+
/* Permission check */
783+
if (!lo_compat_privileges&&
784+
pg_largeobject_aclcheck_snapshot(loDesc->id,
785+
GetUserId(),
786+
ACL_SELECT,
787+
loDesc->snapshot)!=ACLCHECK_OK)
788+
ereport(ERROR,
789+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
790+
errmsg("permission denied for large object %u",
791+
loDesc->id)));
792+
793+
/*
794+
* Compute number of bytes we'll actually read, accommodating nbytes == -1
795+
* and reads beyond the end of the LO.
796+
*/
797+
loSize=inv_seek(loDesc,0,SEEK_END);
798+
if (loSize>offset)
799+
{
800+
if (nbytes >=0&&nbytes <=loSize-offset)
801+
result_length=nbytes;/* request is wholly inside LO */
802+
else
803+
result_length=loSize-offset;/* adjust to end of LO */
804+
}
805+
else
806+
result_length=0;/* request is wholly outside LO */
807+
808+
/*
809+
* A result_length calculated from loSize may not fit in a size_t. Check
810+
* that the size will satisfy this and subsequently-enforced size limits.
811+
*/
812+
if (result_length>MaxAllocSize-VARHDRSZ)
813+
ereport(ERROR,
814+
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
815+
errmsg("large object read request is too large")));
816+
817+
result= (bytea*)palloc(VARHDRSZ+result_length);
818+
819+
inv_seek(loDesc,offset,SEEK_SET);
820+
total_read=inv_read(loDesc,VARDATA(result),result_length);
821+
Assert(total_read==result_length);
822+
SET_VARSIZE(result,result_length+VARHDRSZ);
823+
824+
inv_close(loDesc);
825+
826+
returnresult;
827+
}
828+
829+
/*
830+
* Read entire LO
831+
*/
832+
Datum
833+
lo_get(PG_FUNCTION_ARGS)
834+
{
835+
OidloOid=PG_GETARG_OID(0);
836+
bytea*result;
837+
838+
result=lo_get_fragment_internal(loOid,0,-1);
839+
840+
PG_RETURN_BYTEA_P(result);
841+
}
842+
843+
/*
844+
* Read range within LO
845+
*/
846+
Datum
847+
lo_get_fragment(PG_FUNCTION_ARGS)
848+
{
849+
OidloOid=PG_GETARG_OID(0);
850+
int64offset=PG_GETARG_INT64(1);
851+
int32nbytes=PG_GETARG_INT32(2);
852+
bytea*result;
853+
854+
if (nbytes<0)
855+
ereport(ERROR,
856+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
857+
errmsg("requested length cannot be negative")));
858+
859+
result=lo_get_fragment_internal(loOid,offset,nbytes);
860+
861+
PG_RETURN_BYTEA_P(result);
862+
}
863+
864+
/*
865+
* Create LO with initial contents
866+
*/
867+
Datum
868+
lo_create_bytea(PG_FUNCTION_ARGS)
869+
{
870+
OidloOid=PG_GETARG_OID(0);
871+
bytea*str=PG_GETARG_BYTEA_PP(1);
872+
LargeObjectDesc*loDesc;
873+
intwrittenPG_USED_FOR_ASSERTS_ONLY;
874+
875+
CreateFSContext();
876+
877+
loOid=inv_create(loOid);
878+
loDesc=inv_open(loOid,INV_WRITE,fscxt);
879+
written=inv_write(loDesc,VARDATA_ANY(str),VARSIZE_ANY_EXHDR(str));
880+
Assert(written==VARSIZE_ANY_EXHDR(str));
881+
inv_close(loDesc);
882+
883+
PG_RETURN_OID(loOid);
884+
}
885+
886+
/*
887+
* Update range within LO
888+
*/
889+
Datum
890+
lo_put(PG_FUNCTION_ARGS)
891+
{
892+
OidloOid=PG_GETARG_OID(0);
893+
int64offset=PG_GETARG_INT64(1);
894+
bytea*str=PG_GETARG_BYTEA_PP(2);
895+
LargeObjectDesc*loDesc;
896+
intwrittenPG_USED_FOR_ASSERTS_ONLY;
897+
898+
CreateFSContext();
899+
900+
loDesc=inv_open(loOid,INV_WRITE,fscxt);
901+
inv_seek(loDesc,offset,SEEK_SET);
902+
written=inv_write(loDesc,VARDATA_ANY(str),VARSIZE_ANY_EXHDR(str));
903+
Assert(written==VARSIZE_ANY_EXHDR(str));
904+
inv_close(loDesc);
905+
906+
PG_RETURN_VOID();
907+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201310101
56+
#defineCATALOG_VERSION_NO201310271
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1055,6 +1055,15 @@ DESCR("truncate large object");
10551055
DATA(insertOID=3172 (lo_truncate64PGNSPPGUID121000fffftfv2023"23 20"_null__null__null__null_lo_truncate64_null__null__null_ ));
10561056
DESCR("truncate large object (64 bit)");
10571057

1058+
DATA(insertOID=3457 (lo_createPGNSPPGUID121000fffftfv2026"26 17"_null__null__null__null_lo_create_bytea_null__null__null_ ));
1059+
DESCR("create new large object with content");
1060+
DATA(insertOID=3458 (lo_getPGNSPPGUID121000fffftfv1017"26"_null__null__null__null_lo_get_null__null__null_ ));
1061+
DESCR("read entire large object");
1062+
DATA(insertOID=3459 (lo_getPGNSPPGUID121000fffftfv3017"26 20 23"_null__null__null__null_lo_get_fragment_null__null__null_ ));
1063+
DESCR("read large object from offset for length");
1064+
DATA(insertOID=3460 (lo_putPGNSPPGUID121000fffftfv302278"26 20 17"_null__null__null__null_lo_put_null__null__null_ ));
1065+
DESCR("write data at offset");
1066+
10581067
DATA(insertOID=959 (on_plPGNSPPGUID121000fffftfi2016"600 628"_null__null__null__null_on_pl_null__null__null_ ));
10591068
DATA(insertOID=960 (on_slPGNSPPGUID121000fffftfi2016"601 628"_null__null__null__null_on_sl_null__null__null_ ));
10601069
DATA(insertOID=961 (close_plPGNSPPGUID121000fffftfi20600"600 628"_null__null__null__null_close_pl_null__null__null_ ));

‎src/include/libpq/be-fsstubs.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,13 +25,18 @@ extern Datum lo_export(PG_FUNCTION_ARGS);
2525

2626
externDatumlo_creat(PG_FUNCTION_ARGS);
2727
externDatumlo_create(PG_FUNCTION_ARGS);
28+
externDatumlo_create_bytea(PG_FUNCTION_ARGS);
2829

2930
externDatumlo_open(PG_FUNCTION_ARGS);
3031
externDatumlo_close(PG_FUNCTION_ARGS);
3132

3233
externDatumloread(PG_FUNCTION_ARGS);
3334
externDatumlowrite(PG_FUNCTION_ARGS);
3435

36+
externDatumlo_get(PG_FUNCTION_ARGS);
37+
externDatumlo_get_fragment(PG_FUNCTION_ARGS);
38+
externDatumlo_put(PG_FUNCTION_ARGS);
39+
3540
externDatumlo_lseek(PG_FUNCTION_ARGS);
3641
externDatumlo_tell(PG_FUNCTION_ARGS);
3742
externDatumlo_lseek64(PG_FUNCTION_ARGS);

‎src/test/regress/input/largeobject.source

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -203,5 +203,26 @@ SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
203203
SELECT lo_unlink(loid) FROM lotest_stash_values;
204204
\lo_unlink :newloid
205205

206+
\lo_import 'results/lotest.txt'
207+
208+
\set newloid_1 :LASTOID
209+
210+
SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
211+
\gset
212+
213+
SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
214+
215+
SELECT lo_get(:newloid_1, 0, 20);
216+
SELECT lo_get(:newloid_1, 10, 20);
217+
SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
218+
SELECT lo_get(:newloid_1, 0, 20);
219+
220+
SELECT lo_put(:newloid_1, 4294967310, 'foo');
221+
SELECT lo_get(:newloid_1);
222+
SELECT lo_get(:newloid_1, 4294967294, 100);
223+
224+
\lo_unlink :newloid_1
225+
\lo_unlink :newloid_2
226+
206227
TRUNCATE lotest_stash_values;
207228
DROP ROLE regresslo;

‎src/test/regress/output/largeobject.source

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -391,5 +391,55 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
391391
(1 row)
392392

393393
\lo_unlink :newloid
394+
\lo_import 'results/lotest.txt'
395+
\set newloid_1 :LASTOID
396+
SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
397+
\gset
398+
SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
399+
?column?
400+
----------
401+
t
402+
(1 row)
403+
404+
SELECT lo_get(:newloid_1, 0, 20);
405+
lo_get
406+
-------------------------------------------
407+
8800\0110\0110\0110\0110\0110\0110\011800
408+
(1 row)
409+
410+
SELECT lo_get(:newloid_1, 10, 20);
411+
lo_get
412+
-------------------------------------------
413+
\0110\0110\0110\011800\011800\0113800\011
414+
(1 row)
415+
416+
SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
417+
lo_put
418+
--------
419+
420+
(1 row)
421+
422+
SELECT lo_get(:newloid_1, 0, 20);
423+
lo_get
424+
-------------------------------------------------
425+
8800\011\257\257\257\2570\0110\0110\0110\011800
426+
(1 row)
427+
428+
SELECT lo_put(:newloid_1, 4294967310, 'foo');
429+
lo_put
430+
--------
431+
432+
(1 row)
433+
434+
SELECT lo_get(:newloid_1);
435+
ERROR: large object read request is too large
436+
SELECT lo_get(:newloid_1, 4294967294, 100);
437+
lo_get
438+
---------------------------------------------------------------------
439+
\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000foo
440+
(1 row)
441+
442+
\lo_unlink :newloid_1
443+
\lo_unlink :newloid_2
394444
TRUNCATE lotest_stash_values;
395445
DROP ROLE regresslo;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp