Thread: large objects and printable docs

large objects and printable docs

From
Steve Waldman
Date:
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

Re: large objects and printable docs

From
ghaverla@freenet.edmonton.ab.ca
Date:
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



Re: large objects and printable docs

From
"Robert B. Easter"
Date:
> 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