Thread

1. Trigger - Rewrite question with 6.5beta

   Mike Mascari <mascarim@yahoo.com> — 1999-05-20T16:58:39Z

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

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

   Jan Wieck <jwieck@debis.com> — 1999-05-20T17:26:15Z

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