Re: Storing images as BYTEA or large objects - Mailing list pgsql-general

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;