Hi Misa, That gives me a "ERROR: must be superuser to use server-side lo_import()" on the client. I think this is enforced to preserve file permissions on the server? I appreciate the suggestion, and I will keep it, but I think I found a solution that meets my immediate need.
Thanks! On Thu, May 9, 2013 at 12:31 PM, Misa Simic <misa.si...@gmail.com> wrote: > > > > 2013/5/9 Nelson Green <nelsongree...@gmail.com> > >> Thanks Karl, but I'm trying to do this from a psql shell. I can't use the >> C functions there, can I? >> >> >> On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <k...@denninger.net>wrote: >> >>> On 5/9/2013 11:12 AM, Karl Denninger wrote: >>> >>> On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: >>> >>> Take a look here first : >>> >>> http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html >>> >>> >>> >>> then here : >>> http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html >>> >>> >>> >>> didnt try it myself tho. >>> >>> >>> >>> Most of the time people manipulate bytea's using a higher level >>> programming lang. >>> >>> >>> >>> >>> >>> On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: >>> >>> On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios < >>> ach...@matrix.gatewaynet.com> wrote: >>> >>> why not bytea? >>> >>> >>> Hi Achilleas, >>> >>> Actually I was asking if bytea is the correct datatype, and if so, >>> would someone mind providing a simple example of how to insert and retrieve >>> the image through the psql client. >>> >>> Let's say I have an employee named Paul Kendell, who's employee ID is >>> 880918. Their badge number will be PK00880918, and their badge photo is >>> named /tmp/PK00880918.jpg. What would the INSERT statement look like to put >>> that information into the security_badge table, and what would the SELECT >>> statement look like to retrieve that record? >>> >>> Thanks for your time. >>> >>> >>> >>> much more control, much more information, IMHO. >>> >>> In our DB evolving from an initial 7.1 back in 2001, and currently on >>> 9.0, >>> >>> we have been storing everything binary in bytea's. >>> >>> >>> >>> There are downsides in both solutions, you just have to have good reasons >>> >>> to not use bytea. >>> >>> >>> >>> On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: >>> >>> Good morning list, >>> >>> I am designing a system that will have a table for security badges, and >>> we want to store the ID badge photo. These are small files, averaging about >>> 500K in size. We have made the decision to store the image as a BLOB in the >>> table itself for a variety of reasons. However, I am having trouble >>> understanding just how to do that. >>> >>> The table structures: >>> >>> CREATE TABLE employee >>> ( >>> employee_id INTEGER NOT NULL, >>> employee_lastname VARCHAR(35) NOT NULL, >>> employee_firstname VARCHAR(35) NOT NULL, >>> employee_mi CHAR(1), >>> PRIMARY KEY (employee_id) >>> ); >>> >>> CREATE TABLE security_badge >>> ( >>> badge_number CHAR(10) NOT NULL, >>> employee_id INTEGER NOT NULL >>> REFERENCES employee(employee_id), >>> badge_photo ????, >>> PRIMARY KEY (badge_number) >>> ); >>> >>> What datatype should I use for the badge_photo (bytea?), and what are >>> the commands to insert the picture accessing the server remotely through >>> psql, and to retrieve the photos as well, please? >>> >>> Thanks, >>> Nelson >>> >>> >>> >>> - >>> >>> Achilleas Mantzios >>> >>> IT DEV >>> >>> IT DEPT >>> >>> Dynacom Tankers Mgmt >>> >>> >>> >>> >>> - >>> >>> Achilleas Mantzios >>> >>> IT DEV >>> >>> IT DEPT >>> >>> Dynacom Tankers Mgmt >>> >>> >>> To encode: >>> >>> >>> write_conn = Postgresql communication channel in your software that is >>> open to write to the table >>> >>> char *out; >>> size_t out_length, badge_length; >>> >>> badge_length = function-to-get-length-of(badge_binary_data); /* You >>> have to know how long it is */ >>> >>> out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, >>> &out_length); /* Convert */ >>> >>> That function allocates the required memory for the conversion. You now >>> have an encoded string you can "insert" or "update" with. Once you use it >>> in an "insert" or "update" function you then must "PQfreemem(out)" to >>> release the memory that was allocated. >>> >>> To recover the data you do: >>> >>> PQresult *result; >>> >>> result = PQexec(write_conn, "select badge_photo blah-blah-blah"); >>> .... >>> out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned >>> piece of the tuple and convert it */ >>> >>> "out" now contains the BINARY (decoded) photo data. When done with it >>> you: >>> >>> PQfreemem(out) to release the memory that was allocated. >>> >>> That's the rough outline -- see here: >>> >>> http://www.postgresql.org/docs/current/static/libpq-exec.html >>> >>> -- >>> Karl Denninger >>> k...@denninger.net >>> *Cuda Systems LLC* >>> >>> Oops -- forgot the second parameter on the "PQunescapebytea" call :-) >>> >>> Yeah, that would be bad: >>> >>> size_t out_length; >>> >>> out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the >>> returned piece of the tuple and convert it */ >>> >>> Otherwise, being binary data, how would you know how long it is? :-) >>> >>> BTW I use these functions extensively in my forum code and have stored >>> anything from avatars (small image files) to multi-megabyte images. Works >>> fine. You have to figure out what the type of image is, of course (or know >>> that in advance) and tag it somehow if you intend to do something like >>> display it on a web page as the correct mime type content header has to be >>> sent down when the image is requested. What I do in my application is >>> determine the image type at storage time (along with width and height and a >>> few other things) and save it into the table along with the data. >>> >>> >>> -- >>> Karl Denninger >>> k...@denninger.net >>> *Cuda Systems LLC* >>> >> >> > > > You can try: > > > create or replace function bytea_import(p_path text) > returns bytea > language plpgsql as $$ > declare > l_oid oid; > r record; > b_result bytea; > begin > p_result := ''; > select lo_import(p_path) into l_oid; > for r in ( select data > from pg_largeobject > where loid = l_oid > order by pageno ) loop > b_result = b_result || r.data; > end loop; > perform lo_unlink(l_oid); > return b_result; > end;$$; > > then when you want to insert a row in a table: > > INSERT INTO security_badge VALUES('badge_no1', 1, > bytea_import('pathtothefile')) >
