plpgsql FOR LOOP CTE problem ?

["Day, David" <dday@xxxxxxxxxx>]

Hi,

 

I am working on  a plpgsql function that is not acting as I would hope.

I am working with the Postgres 9.3 beta load and would like to

solicit some feedback.

 

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

 

The function later bombs on the insert statement as tid_seq and ws_grp_seq columns

Have a not null requirement. 

 

Any thoughts ?

 

 

Thanks

 

Dave

 

 

 

 

The function basics are:

 

CREATE OR REPLACE FUNCTION admin.activate_translator_user (ws_id integer)

  RETURNS void AS

$BODY$

 DECLARE

     drow admin.translator_member%ROWTYPE;  -- deleted row holder

     wsrow RECORD;

     patt CHARACTER VARYING;

 

BEGIN

    -- Remove current input ws_id subsection of the translator but grab some

    -- sequenceing information from it.

   

     FOR drow IN

         WITH drows AS (                                                              -- Runs as expected from psql command line

             DELETE FROM admin.translator_member

                    WHERE tu_id = ws_id RETURNING *

          )

          SELECT translator_id, MIN(tid_seq)

                AS tid_seq, MIN(ws_grp_seq) AS ws_grp_seq

           FROM drows GROUP BY translator_id

     LOOP

         Raise notice ' TID_seq % WS_seq % TID % ', drow.tid_seq, drow.ws_grp_seq,  drow.translator_id;

        

         -- Replace the removed subsection of relevant translation sets.

         FOR wsrow IN

             SELECT * FROM admin.translator_user_mbr

                  WHERE tu_id = ws_id

                  ORDER BY obj_seq ASC

         LOOP

            -- On activation refresh the pattern content from the pattern

            -- table if pattern_id is not zero.

            IF wsrow.pattern_id IS NOT NULL AND wsrow.pattern_id != 0

            THEN

                SELECT pattern INTO patt FROM admin.pattern

                       WHERE pattern_id = wsrow.pattern_id;

             ELSE

                 patt = 'NULL';

             END IF;

 

             INSERT INTO admin.translator_member (

                        "name", "tu_id", "translator_id",

                        "tid_seq", "ws_grp_seq", "obj_seq", …..

                            ) VALUES (

                        NULL, wsrow."tu_id", drow."translator_id",

                        drow."tid_seq", drow."ws_grp_seq", wsrow."obj_seq",….. _);

 

         END LOOP; -- wsrow

      END LOOP; -- drow

 

               

 END

 $BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;