Обсуждение: Trigger - Rewrite question with 6.5beta

While on the subjects of triggers, is this the 
proper behavior?

t=> SELECT version();
version                                                      
------------------------------------------------------
PostgreSQL 6.5.0 on i586-pc-linux-gnu, 
compiled by gcc 2.7.2.
(1 row)

t=> CREATE TABLE TEST1 (
t-> id int4, 
t-> value text not null);
CREATE

t=> CREATE TABLE TEST2 (
t-> value text not null);
CREATE

t=> CREATE SEQUENCE TESTSEQ;
CREATE

t=> CREATE TRIGGER T_TEST1 BEFORE INSERT ON TEST1 
t-> FOR EACH ROW EXECUTE PROCEDURE 
t-> autoinc(id, TESTSEQ);
CREATE

t=> INSERT INTO TEST2 VALUES ('hello');
INSERT 2497567 1

t=> INSERT INTO TEST2 VALUES ('hello');
INSERT 2497568 1

t=> INSERT INTO TEST2 VALUES ('goodbye');
INSERT 2497569 1

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|goodbye2|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?

Thanks for any info, 

Marcus Mascari (mascarim@yahoo.com)

P.S. The autoinc() is the one from /contrib




_____________________________________________________________
Do You Yahoo!?
Free instant messaging and more at http://messenger.yahoo.com

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