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'))