Re: Me And My Database - Mailing list pgsql-general

From Leif B. Kristensen
Subject Re: Me And My Database
Date
Msg-id 200606141104.19324.leif@solumslekt.org
Whole thread Raw
In response to Re: [pgsql-advocacy] Me And My Database  ("Jim C. Nasby" <jnasby@pervasive.com>)
Responses Re: Me And My Database
List pgsql-general
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

pgsql-general by date:

Previous
From: "Daniel McBrearty"
Date:
Subject: table has many to many relationship with itself - how to implement?
Next
From: Martijn van Oosterhout
Date:
Subject: Re: Me And My Database