codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>35.4. Server-Side Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="lo-interfaces.html" title="35.3. Client Interfaces" /><link rel="next" href="lo-examplesect.html" title="35.5. Example Program" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">35.4. Server-Side Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="lo-interfaces.html" title="35.3. Client Interfaces">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="largeobjects.html" title="Chapter 35. Large Objects">Up</a></td><th width="60%" align="center">Chapter 35. Large Objects</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="lo-examplesect.html" title="35.5. Example Program">Next</a></td></tr></table><hr /></div><div class="sect1" id="LO-FUNCS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">35.4. Server-Side Functions <a href="#LO-FUNCS" class="id_link">#</a></h2></div></div></div><p>3 Server-side functions tailored for manipulating large objects from SQL are4 listed in <a class="xref" href="lo-funcs.html#LO-FUNCS-TABLE" title="Table 35.1. SQL-Oriented Large Object Functions">Table 35.1</a>.5 </p><div class="table" id="LO-FUNCS-TABLE"><p class="title"><strong>Table 35.1. SQL-Oriented Large Object Functions</strong></p><div class="table-contents"><table class="table" summary="SQL-Oriented Large Object Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">6 Function7 </p>8 <p>9 Description10 </p>11 <p>12 Example(s)13 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">14 <a id="id-1.7.4.9.3.2.2.1.1.1.1" class="indexterm"></a>15 <code class="function">lo_from_bytea</code> ( <em class="parameter"><code>loid</code></em> <code class="type">oid</code>, <em class="parameter"><code>data</code></em> <code class="type">bytea</code> )16 → <code class="returnvalue">oid</code>17 </p>18 <p>19 Creates a large object and stores <em class="parameter"><code>data</code></em> in it.20 If <em class="parameter"><code>loid</code></em> is zero then the system will choose a21 free OID, otherwise that OID is used (with an error if some large22 object already has that OID). On success, the large object's OID is23 returned.24 </p>25 <p>26 <code class="literal">lo_from_bytea(0, '\xffffff00')</code>27 → <code class="returnvalue">24528</code>28 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">29 <a id="id-1.7.4.9.3.2.2.2.1.1.1" class="indexterm"></a>30 <code class="function">lo_put</code> ( <em class="parameter"><code>loid</code></em> <code class="type">oid</code>, <em class="parameter"><code>offset</code></em> <code class="type">bigint</code>, <em class="parameter"><code>data</code></em> <code class="type">bytea</code> )31 → <code class="returnvalue">void</code>32 </p>33 <p>34 Writes <em class="parameter"><code>data</code></em> starting at the given offset within35 the large object; the large object is enlarged if necessary.36 </p>37 <p>38 <code class="literal">lo_put(24528, 1, '\xaa')</code>39 → <code class="returnvalue"></code>40 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">41 <a id="id-1.7.4.9.3.2.2.3.1.1.1" class="indexterm"></a>42 <code class="function">lo_get</code> ( <em class="parameter"><code>loid</code></em> <code class="type">oid</code> [<span class="optional">, <em class="parameter"><code>offset</code></em> <code class="type">bigint</code>, <em class="parameter"><code>length</code></em> <code class="type">integer</code> </span>] )43 → <code class="returnvalue">bytea</code>44 </p>45 <p>46 Extracts the large object's contents, or a substring thereof.47 </p>48 <p>49 <code class="literal">lo_get(24528, 0, 3)</code>50 → <code class="returnvalue">\xffaaff</code>51 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>52 There are additional server-side functions corresponding to each of the53 client-side functions described earlier; indeed, for the most part the54 client-side functions are simply interfaces to the equivalent server-side55 functions. The ones just as convenient to call via SQL commands are56 <code class="function">lo_creat</code><a id="id-1.7.4.9.4.2" class="indexterm"></a>,57 <code class="function">lo_create</code>,58 <code class="function">lo_unlink</code><a id="id-1.7.4.9.4.5" class="indexterm"></a>,59 <code class="function">lo_import</code><a id="id-1.7.4.9.4.7" class="indexterm"></a>, and60 <code class="function">lo_export</code><a id="id-1.7.4.9.4.9" class="indexterm"></a>.61 Here are examples of their use:62 63</p><pre class="programlisting">64CREATE TABLE image (65 name text,66 raster oid67);68 69SELECT lo_creat(-1); -- returns OID of new, empty large object70 71SELECT lo_create(43213); -- attempts to create large object with OID 4321372 73SELECT lo_unlink(173454); -- deletes large object with OID 17345474 75INSERT INTO image (name, raster)76 VALUES ('beautiful image', lo_import('/etc/motd'));77 78INSERT INTO image (name, raster) -- same as above, but specify OID to use79 VALUES ('beautiful image', lo_import('/etc/motd', 68583));80 81SELECT lo_export(image.raster, '/tmp/motd') FROM image82 WHERE name = 'beautiful image';83</pre><p>84 </p><p>85 The server-side <code class="function">lo_import</code> and86 <code class="function">lo_export</code> functions behave considerably differently87 from their client-side analogs. These two functions read and write files88 in the server's file system, using the permissions of the database's89 owning user. Therefore, by default their use is restricted to superusers.90 In contrast, the client-side import and export functions read and write91 files in the client's file system, using the permissions of the client92 program. The client-side functions do not require any database93 privileges, except the privilege to read or write the large object in94 question.95 </p><div class="caution"><h3 class="title">Caution</h3><p>96 It is possible to <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a> use of the97 server-side <code class="function">lo_import</code>98 and <code class="function">lo_export</code> functions to non-superusers, but99 careful consideration of the security implications is required. A100 malicious user of such privileges could easily parlay them into becoming101 superuser (for example by rewriting server configuration files), or could102 attack the rest of the server's file system without bothering to obtain103 database superuser privileges as such. <span class="emphasis"><em>Access to roles having104 such privilege must therefore be guarded just as carefully as access to105 superuser roles.</em></span> Nonetheless, if use of106 server-side <code class="function">lo_import</code>107 or <code class="function">lo_export</code> is needed for some routine task, it's108 safer to use a role with such privileges than one with full superuser109 privileges, as that helps to reduce the risk of damage from accidental110 errors.111 </p></div><p>112 The functionality of <code class="function">lo_read</code> and113 <code class="function">lo_write</code> is also available via server-side calls,114 but the names of the server-side functions differ from the client side115 interfaces in that they do not contain underscores. You must call116 these functions as <code class="function">loread</code> and <code class="function">lowrite</code>.117 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="lo-interfaces.html" title="35.3. Client Interfaces">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="largeobjects.html" title="Chapter 35. Large Objects">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="lo-examplesect.html" title="35.5. Example Program">Next</a></td></tr><tr><td width="40%" align="left" valign="top">35.3. Client Interfaces </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 35.5. Example Program</td></tr></table></div></body></html>