On 8/31/06, Randall Lucas <rlucas@tercent.com> wrote:
> Now that I have this query, in order to make my case, I need to "sign
> off" on all of the individual data that went into it. I would like to
> do something like:
>
> select last_query_shown_tuples();
> schema | table_name | pk_columns | pk_values
> --------+---------------+------------+-----------
> public | company | [id] | [2]
> public | officer | [id] | [3]
> public | insider_trade | [id] | [1]
> (3 rows)
right. in sql, except for a few miscellaneous things that are session
based, information state is kept in the tables and if you want to keep
things relational all information should be have a primary key. Your
last_query_shown_tuples function should be a simple select statement
returning information written with an id based on some criteria. You
could call this table 'investigation' and put in it the information
necesasry to backtrack to the other tables.
A key tenet of relational thinking is to reduce all information to its
functional dependencies, and to try and avoid as much as possible
keeping information state in the data in a declarative sense.
last_query_shown_tuples() is imo a violation in that sense. so is
currval() in fact, because given the same database defined by its data
it could give different answers depending on things leading up to how
the data was put there. therefore, it is a hack, albeit a very useful
one.
One more thing I would like to suggest is to not automatically use a
serial column to define a primary key. While this is a good idea in
many cases for various reasons, it can lead to lazy thinking and in
extreme cases bad performance. a good way to approach sequences is to
define the natural key and use the generated ID as an alternative
candidate key if you want to keep things tight.
merlin