Re: [HACKERS] Trigger - Rewrite question with 6.5beta

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) #