From: Rod Taylor <pg@rbt.ca>
To: operationsengineer1@yahoo.com
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <20060615205922.4834.qmail@web33301.mail.mud.yahoo.com>
References: <20060615205922.4834.qmail@web33301.mail.mud.yahoo.com>
Date: Thu, 15 Jun 2006 17:21:46 -0400
Message-Id: <1150406506.777.468.camel@home>
Mime-Version: 1.0
Subject: Re: SQL Technique Question
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Thu, 2006-06-15 at 13:59 -0700, operationsengineer1@yahoo.com wrote:
> i frequently join certain tables together in various
> tables.

> is it a good practice to leave this included in the
> queries, as is, or should i factor it out somehow?  if
> i should factor it, how do i do so?

Future proofing selects queries is difficult because they never throw
errors. They just give different results. What you really want is an
ASSERTION to disallow bad entries from being created in the first place
but PostgreSQL doesn't do those yet. Yes, you can do it with triggers
but those are annoying to create by the hundreds for development only
purposes.

I would tend to add all of the columns to select be selected out and
have assertions in the code. This way you can detect incorrect values in
development and disable those safety's for production.

        select t_inspect_result.inspect_result_pass
                , t_inspect.serial_number_id
                , t_serial_number.serial_number_id ...
        
        assert(t_inspect.serial_number_id =
        t_serial_number.serial_number_id);

--