Re: plpgsql FOR LOOP CTE problem ? - Mailing list pgsql-general

Hi Tom,

That's a good thought on the duplicated variable names.
The two table involved do have many common column names
But the inputs are explicitly referencing tables on the inserts.
Would not seem to be possible to confuse them.

In trying to make a simplified test case,  I came across
Something else I thought odd.

My observation is that if the inner loop references the
"drow" LOOP variable the actual deletion in table aaa by the outer loop
is not taking place as I would hope.
RE: the inner loop insert aaa table fails.
 I get a duplicate primary key violation on the insert in my sample function below.

So far, for my test case below, I do not see the original reported error where the MINed
Column value is NULL.  Still trying to replicate that in a simplified version.

The purpose of the function is to replace in a master table (aaa)  a subsection that
A user has modify privileges on within a separate work space table (bbb)  .

I am continuing to explore replicating the original problem.  Thought this tangent
might suggest something to you. ( hopefully that I'm not a total idiot )

Thanks

Dave


CREATE TABLE admin.aaa (
  translator_id integer,
  tid_seq integer NOT NULL,
  ws_grp_seq integer,
  PRIMARY KEY (translator_id, tid_seq)
  );

  INSERT INTO admin.aaa (translator_id,tid_seq, ws_grp_seq ) VALUES
    (1,1,1),
    (1,2,1),
    (1,3,1),
    (2,1,1),
    (2,2,1),
    (2,3,1);

 CREATE TABLE admin.bbb (
  translator_id integer,
  tid_seq integer,
  ws_grp_seq integer,
  PRIMARY KEY (translator_id, tid_seq)
  );

  INSERT INTO admin.bbb (translator_id,tid_seq, ws_grp_seq ) VALUES
    (1,1,1),
    (1,2,1),
    (1,3,1);

 CREATE OR REPLACE FUNCTION admin.activate_translator_user1 (ws_id integer)
  RETURNS void AS
 $BODY$

 DECLARE
     drow admin.aaa%ROWTYPE;  -- deleted row holder
     nrow admin.bbb%ROWTYPE;   --

 BEGIN
    -- Remove current subsection of the translator but grab some
    -- sequenceing information from it.


     FOR drow IN
         WITH xrows AS (
             DELETE FROM admin.aaa
                    WHERE translator_id = ws_id RETURNING *
          )
          SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
           FROM xrows GROUP BY translator_id
     LOOP
         Raise notice ' TID_seq % WS_seq % TID % ', drow.tid_seq, drow.ws_grp_seq,  drow.translator_id;  -- output is
correct(all 3 cases ? )  
         FOR nrow IN
             SELECT * FROM admin.bbb WHERE translator_id = ws_id
         LOOP
            INSERT INTO admin.aaa ( translator_id, tid_seq, ws_grp_seq ) VALUES
                                  (  nrow.translator_id, nrow.tid_seq, nrow.tid_seq );    -- works correctly.
 --                                 (  nrow.translator_id, drow.tid_seq, nrow.tid_seq );  -- duplicate primary key
error.
 --                                 (  drow.translator_id, drow.tid_seq, drow.tid_seq );  -- duplicate prinary key
errors-  

          END LOOP; -- drow
     END LOOP; -- drow


 END
 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


select admin.activate_translate_user1(1);



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, August 09, 2013 10:20 AM
To: Day, David
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

"Day, David" <dday@redcom.com> writes:
> Looking at  the outermost for loop of the function below,  If I run
> this CTE query from the psql command line I am returned what I expect
> for values  for translator_id and the Aggregating MIN functions. I restore the experimental data and now run the
function.
> In the context of this function I get a valid  translator_id , But the
> MINed columns are NULL ????

Perhaps those column names duplicate variable names within the function, and what's being returned is the values of the
variables?

If you think there's a bug here, you'd need to provide a self-contained test case.

            regards, tom lane