Re: large objects and printable docs

> 2) From what I can tell, all of the standard types in Postgres max out
> at than 8k, but there is some sort of large object support. Large object
> support seems to be documented only in the programmer's guide; there is
> no mention of these in the data types section of the users' guide.
> Looking at examples from the programmers guide, it looks as though there
> is a non-standard SQL data type called oid, some support for working
> with these in SQL directly, but much more support for working with them
> through interface APIs. Does postgres 7 have any support for SQL3
> blob/clob datatypes (stored in SQL as such)?

There is no BLOB or CLOB data types in postgres. Large objects are not really a
data type. A large object is more or less a table (called a class in
postgresql). Its a special table that is not visible like normal tables and
provide storage of binary data. Access to a large object class is via a set of
special functions, such as lo_import, lo_export, lo_creat, lo_open, lo_unlink,
lo_lseek, loread, lowrite, and lo_tell. They mirror the unix file functions.
So, a large object is like a file/table stored in the database. If you select
* from pgclass, you can see the large objects as relname like 'xin'. When you
create a large object with lo_creat, it returns an OID used in the other
functions. And yes, you can create a table with an attribute of type OID to
hold large object or even row OIDs. Every row in a table, and tables themselves
have oids.

Here is how I store images:

CREATE TABLE images (
id SERIAL PRIMARY KEY,

fname VARCHAR(50) NOT NULL
CHECK (fname ~ '$[-_a-zA-Z0-9]+$'),
-- names have to be simple, like file_name-01 etc, no spaces

ftype VARCHAR(3) NOT NULL
CHECK (ftype IN ('gif', 'jpg', 'png') ),
-- only gif, jpg, and png types

image OID NOT NULL,
-- the large object

width INTEGER NOT NULL
CHECK ( width <= 468 ),

height INTEGER NOT NULL
CHECK ( height <= 468 ),

sizebytes INTEGER NOT NULL
CHECK ( sizebytes <= 131072 ),

create_date TIMESTAMP DEFAULT 'now',

users_id INTEGER REFERENCES users
ON UPDATE CASCADE
ON DELETE SET NULL
INITIALLY DEFERRED
-- users id that uploaded the image
-- if null, then system image
);
CREATE UNIQUE INDEX images_fname_ftype_idx ON images ( fname, ftype );
-- the combination fname.ftype must be unique

CREATE RULE image_delete_lo AS
ON DELETE TO images
DO SELECT lo_unlink(old.image);
-- This rule will delete the large object automatically when its row is deleted.

Each language you use, php, perl, python, tcl, etc each have varying support
for using large objects. C language using the libpq has all lo functions.

You can use the psql program to access many lo functions, which you can use in
SELECT statements to import/export files into and outof large objects.

In the example above, you create your large object first and get a OID to it,
then you insert into the images table.

I've read in some other other mail lists that BLOB and CLOB will be supported
in some future release, maybe 7.2. That will be quite a while from now still.

If you need to just store large amounts of nonbinary (text) data, then consider
something like this:

-- Load the TCL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION pltcl_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/pltcl.so'
LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl'
HANDLER pltcl_call_handler
LANCOMPILER 'PL/tcl';

-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'
LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';

--------------------------------------------------------------------------------
--
-- Text storage
--

-- putlgtext - generic function to store text into the
-- specified text storage table.
-- The table specified in $1 should have the following
-- fields:
-- id, text_seq, text_block
--
-- $1 is the name of the table into which $3 is stored
-- $2 is the id of the text and references id in another table
-- $3 is the text to store, which is broken into chunks.
-- returns 0 on success
-- nonzero otherwise
CREATE FUNCTION putlgtext (TEXT, INTEGER, TEXT) RETURNS INTEGER AS '

set i_table $1
set i_id $2
set i_t $3
set i_seq 0

while { $i_t != {} } {
set tblock [string range $i_t 0 7000]
set i_t [string range $i_t [expr 7000 + 1] end]
spi_exec "INSERT INTO $i_table (id, text_seq, text_block) VALUES ( $i_id , $i_seq , \'$tblock\' )"
incr i_seq
}
return 0
' LANGUAGE 'pltcl';

-- getlgtext - like putlgtext, this is a generic
-- function that does the opposite of putlgtext
-- $1 is the table from which to get TEXT
-- $2 is the id of the text to get
-- returns the text concatenated from one or more rows
CREATE FUNCTION getlgtext(TEXT, INTEGER) RETURNS TEXT AS '
set o_text {}
spi_exec -array q_row "SELECT text_block FROM $1 WHERE id = $2 ORDER BY text_seq" {
append o_text $q_row(text_block)
}
return $o_text
' LANGUAGE 'pltcl';

-- largetext exists just to hold an id and a dummy 'lgtext' attribute.
-- This table's trigger function provides for inserting and updating
-- into largetext_block. The text input to lgtext actually gets
-- broken into chunks and stored in largetext_block.
-- Deletes to this table will chain to largetext_block automatically
-- by referential integrity on the id attribute.
-- Selects have to be done using the getlgtext function.
CREATE TABLE largetext (
id INTEGER PRIMARY KEY,
lgtext TEXT -- dummy field
);

-- This table must have the field names as they are.
-- These attribute names are expected by put/getlgtext.
CREATE TABLE largetext_block (
id INTEGER NOT NULL
REFERENCES largetext
ON DELETE CASCADE
ON UPDATE CASCADE,

text_seq INTEGER NOT NULL,

text_block TEXT,

PRIMARY KEY (id, text_seq)
);
CREATE SEQUENCE largetext_seq;

-- SELECT:
-- SELECT id AS the_id FROM largetext;
-- SELECT getlgtext('largetext_block', id) FROM largetext WHERE id = the_id;

-- INSERT:
-- INSERT INTO largetext (lgtext) values ('.......');

-- DELETE:
-- DELETE FROM largetext WHERE id = someid;
-- deletes from largetext and by referential
-- integrity, from largetext_text all associated block rows.
CREATE FUNCTION largetext_trigfun() RETURNS OPAQUE AS '
switch $TG_op {
INSERT {
spi_exec "SELECT nextval(\'largetext_seq\') AS new_id"
set NEW($1) $new_id
spi_exec "SELECT putlgtext(\'largetext_block\', $new_id, \'$NEW($2)\') AS rcode"
if { $rcode != 0 } then { return SKIP }
}
UPDATE {
if { $NEW($2) != {} } then {
spi_exec "DELETE FROM largetext_text WHERE id = $OLD($1)"
spi_exec "SELECT putlgtext(\'largetext_block\', $OLD($1), \'$NEW($2)\') AS rcode"
if { $rcode != 0 } then { return SKIP }
}
}
}
set NEW($2) "ok"
return [array get NEW]
' LANGUAGE 'pltcl';

-- Set the function as trigger for table largetext
CREATE TRIGGER largetext_trig BEFORE INSERT OR UPDATE
ON largetext FOR EACH ROW EXECUTE
PROCEDURE largetext_trigfun(id,lgtext);

Maybe not the best way to do things, but might work.

--
Robert B. Easter
reaster(at)comptechnews(dot)com