Thread: reuse RECORD in function 'select into'?

In the function below I do four "select into" actions to check if
particular values already exist in any one of several tables.

Can I reuse the RECORD for each 'select into'? The RECORDs are
presumably empty after each select into, as the function needs FOUND to
be false to continue.

Also, should I "RETURN 0" after a RAISE EXCEPTION?

Thanks
Rory



CREATE OR REPLACE FUNCTION
fn_c1_create_board ( integer, integer, varchar, varchar, varchar) RETURNS INTEGER
    AS '
DECLARE
    creator       ALIAS for $1;
    typer         ALIAS for $2;
    name          ALIAS for $3;
    email         ALIAS for $4;
    description   ALIAS for $5;
    recone        RECORD;
    rectwo        RECORD;
    recthree      RECORD;
    recfour       RECORD;
BEGIN

    -- more extensive checking to be done in client program

    IF creator IS NULL
    THEN
        RAISE EXCEPTION ''no creator found at fn_c1_create_board'';
    END IF;

    IF typer IS NULL
    THEN
        RAISE EXCEPTION ''no typer found at fn_c1_create_board'';
    END IF;

    IF name IS NULL
    THEN
        RAISE EXCEPTION ''no name found at fn_c1_create_board'';
    END IF;

    IF email IS NULL
    THEN
        RAISE EXCEPTION ''no email found at fn_c1_create_board'';
    END IF;

    IF description IS NULL
    THEN
        RAISE EXCEPTION ''no description found at fn_c1_create_board'';
    END IF;

    -- find all in people and boards who have a name like this one.
    -- if found, abort creation

    SELECT into recone
        n_id
    FROM
        boards
    WHERE
        t_name ~* name;

    IF FOUND
    THEN
        RAISE EXCEPTION ''board with same name found at fn_c1_create_board'';
    END IF;

    SELECT into rectwo
        n_id
    FROM
        people
    WHERE
    t_nickname ~* name;

    IF FOUND
    THEN
        RAISE EXCEPTION ''person with same nickname found at fn_c1_create_board'';
    END IF;

    SELECT into recthree
        n_id
    FROM
        people
    WHERE
        t_email ~* email;

    IF FOUND
    THEN
        RAISE EXCEPTION ''person with same email found at fn_c1_create_board'';
    END IF;

    SELECT into recfour
        n_id
    FROM
        boards
    WHERE
        t_email ~* email;

    IF FOUND
    THEN
        RAISE EXCEPTION ''board with same email found at fn_c1_create_board'';
    END IF;

    -- ok, if we have got here, its ok to make the board!

    INSERT INTO
        boards
        (n_creator, n_type, t_name, t_email, t_description)
    VALUES
        (creator, typer, name, email, description);

    RETURN 1;

END;'
    LANGUAGE plpgsql;


--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Rory,

> In the function below I do four "select into" actions to check if
> particular values already exist in any one of several tables.
>
> Can I reuse the RECORD for each 'select into'? The RECORDs are
> presumably empty after each select into, as the function needs FOUND to
> be false to continue.

You certainly can, technically.  In fact, you don't even need the RECORD to be
empty.

But you want to be *real* careful doing this.  Re-using variables for
different purposes (in procedural languages), in general, is a very bad idea
unless you are desperately shourt of memory.   It's far too easy to lose
track of a re-used variable and waste hours debugging.

O'Reilly's "PL/SQL Programming"  has an excellent chapter on good programming
practives for SQL-procedural languages that I would love to razor out and
distribute as a pamphlet.   It would be worth a gander the next time you have
a long lunch in your local tech book store.

> Also, should I "RETURN 0" after a RAISE EXCEPTION?

Yes, to make the parser happy.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Thanks again for your help, Josh.

On 27/05/03, Josh Berkus (josh@agliodbs.com) wrote:
> > In the function below I do four "select into" actions to check if
> > particular values already exist in any one of several tables.
> >
> > Can I reuse the RECORD for each 'select into'? The RECORDs are
> > presumably empty after each select into, as the function needs FOUND to
> > be false to continue.
>
> You certainly can, technically.  In fact, you don't even need the RECORD to be
> empty.

That makes sense, although my "Postgresql Essential Reference" book
selects into an "EmpRec" record. FOUND eq true if one (or more) rows
returned, I see.

> But you want to be *real* careful doing this.  Re-using variables for
> different purposes (in procedural languages), in general, is a very bad idea
> unless you are desperately shourt of memory.   It's far too easy to lose
> track of a re-used variable and waste hours debugging.
>
> O'Reilly's "PL/SQL Programming"  has an excellent chapter on good programming
> practives for SQL-procedural languages that I would love to razor out and
> distribute as a pamphlet.   It would be worth a gander the next time you have
> a long lunch in your local tech book store.

Is this the Oracle PL/SQL book?

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>