Re: Me And My Database

On Wednesday 7. June 2006 00:10, Jim C. Nasby wrote:

>Also, the commentary about how MySQL is faster isn't very clear. Are
> you using MySQL as some kind of result cache? When you get to running
> actual concurrent access on the website, you could well find yourself
> very disappointed with the performance of MyISAM and it's table-level
> locking. There's probably also some gains to be had on the PostgreSQL
> performance.

I've rewritten that passage to make it clearer what it's about. I've
also included the complete table definitions, along with my views and
functions. The article is still at
<http://solumslekt.org/forays/exodus.php>.

One question: When I have a function like this:

CREATE OR REPLACE FUNCTION get_source_text(INTEGER) RETURNS TEXT AS $$
DECLARE
src sources%ROWTYPE;
mystring TEXT;
BEGIN
SELECT * FROM sources INTO src WHERE source_id = $1;
mystring := src.large_text;
IF src.parent_id <> 0 THEN
mystring := get_source_text(src.parent_id) || ' ' || mystring;
END IF;
RETURN mystring;
END;
$$ LANGUAGE plpgsql;

What do you suggest that I write instead of "SELECT * FROM sources INTO
src", when src is defined as sources%ROWTYPE? The table sources is
defined as:

CREATE TABLE sources (
source_id INTEGER PRIMARY KEY,
parent_id INTEGER NOT NULL REFERENCES sources (source_id),
small_text VARCHAR(50) NOT NULL DEFAULT '',
large_text TEXT NOT NULL DEFAULT ''
);

I only need (source_id, parent_id, large_text) in the query. The
small_text column is largely unused, but holds at most 50 chars.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE