Thread

1. large objects and printable docs

   Steve Waldman <swaldman@mchange.com> — 2000-06-05T06:35:13Z

   Hi,
   
   I've got a few rather illiterate questions; I'd be grateful if anyone
   would take the time to answer them.
   
   1) Is there anywhere where postscript or pdf renderings of the docs can
   be downloaded on-line? I'd prefer to just download and print these
   rather than having to set up a whole bunch of sgml tools.
   
   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)?
   
   Many thanks for any help.
   
       Steve
   
   

2. Re: large objects and printable docs

   ghaverla@freenet.edmonton.ab.ca — 2000-06-05T11:54:26Z

   On Mon, 5 Jun 2000, Steve Waldman wrote:
   
   > I've got a few rather illiterate questions; I'd be grateful if anyone
   > would take the time to answer them.
   
   Hey, I'm almost as illiterate as you, vis a vis PostgreSQL.
   
   > 1) Is there anywhere where postscript or pdf renderings of the docs can
   > be downloaded on-line? I'd prefer to just download and print these
   > rather than having to set up a whole bunch of sgml tools.
   
   If you visit the website, under
    Info Central
     Documentation
      Published Book
   you will see a link to downloading a PDF of the book, which as of about a
   week ago, is still being written.  I haven't checked lately.
   
   > 2) From what I can tell, all of the standard types in Postgres max out
   > at than 8k,
   
   Sort of.  I believe this is a tuple (or row) limit.  If you have 8
   text or varchar types in a tuple, on average they are limited to 
   about 1k each.  Or this is how I interpret the docs.  I haven't
   tried pushing it.
   
   >          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.
   
   There is some mention in the book, and a handful of examples
   in the various guides and in archives at DejaNews.
   
   A large object is sort of an anonymous thing.  You can tell that
   something was stored, but unless you write/export the data back 
   into user space, you can't do anything with it.
   
   > Looking at examples from the programmers guide, it looks as though there
   > is a non-standard SQL data type called oid,
   
   I don't think it is quite so much that OID is non-standard, I
   think every dbase which is capable of handling large objects
   has something analagous to oid.  It is a pointer to storage
   if you think C.
   
   >                                            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)?
   
   This I don't know.  My perl stuff which was inputting large objects
   wasn't written for 7.  I don't know how things have changed.
   
   Gord
   
   Matter Realisations     http://www.materialisations.com/
   Gordon Haverland, B.Sc. M.Eng. President
   101  9504 182 St. NW    Edmonton, AB, CA  T5T 3A7
   780/481-8019            ghaverla @ freenet.edmonton.ab.ca
   
   
   
   

3. Re: large objects and printable docs

   Robert B. Easter <reaster@comptechnews.com> — 2000-06-05T12:38:12Z

   > 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@comptechnews.com