Re: Storing images as BYTEA or large objects

[John DeSoi <desoi@xxxxxxxxxx>]

On Feb 13, 2008, at 2:53 PM, Koen Vermeer wrote:

> I'll check to see what the options are for reading in the data in PHP.
> Thanks for the help!


If you use prepared statements, you don't need to do anything special  
at all for bytea with PHP. No worries about escaping and all that.

Using the schema below and a simple prepared statement API (http://pgedit.com/resource/php/pgfuncall 
), I can insert/load documents with a single line like:

$db->blob_insert($content);

$content = $db->blob_content($this->object_ref);



John DeSoi, Ph.D.



--
-- blobs
--
create table blob (
	dbid serial primary key,
	content bytea
);


create or replace function blob_insert(p_content bytea)
returns integer as $$
declare
	new_dbid integer = nextval(pg_get_serial_sequence('blob', 'dbid'));
begin
	insert into blob (dbid, content) values (new_dbid, p_content);
	return new_dbid;
end;
$$ language plpgsql;



create or replace function blob_update(p_dbid integer, p_content bytea)
returns integer as $$
begin
	update blob set content = p_content where dbid = p_dbid;
	if found then
		return 1;
	else
		return 0;
	end if;
end;
$$ language plpgsql;



create or replace function blob_content(p_dbid integer)
returns bytea as $$
declare
	v_content bytea;
begin
	select into v_content content from blob where dbid = p_dbid;
	return v_content;
end;
$$ language plpgsql;


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend