Re: Storing images as BYTEA or large objects

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;