Marcus Mascari wrote:
>
> While on the subjects of triggers, is this the
> proper behavior?
> [...]
>
> t=> INSERT INTO TEST1 (value)
> t-> SELECT DISTINCT value FROM TEST2;
> NOTICE: testseq.nextval: sequence was re-created
> INSERT 0 3
>
> t=> SELECT * FROM TEST1;
> id|value
> --+-------
> 1|goodbye
> 2|hello
> 3|hello
> (3 rows)
>
> I guess I was expecting the DISTINCT in the
> SELECT to suppress the fetching of the second
> 'hello' record, then the insert is performed, and,
> while the insert is performed, the trigger procedure
> is executed to fetch the sequence value for 2
> rows, not 3. Is this related to the same
> conditions which make the use of DISTINCT on VIEWS
> problematic?
Similar - i guess. Must be the fact that the distinct clause
doesn't specify the columns. Thus it is an empty list and
treated as "DISTINCT ON id,value" because the targetlist got
expanded to match the result tables schema.
So at least in the case of INSERT ... SELECT the list of
distinct columns must be set to the columns in the targetlist
if it is empty (no columns specified by user) before
targetlist expansion.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #