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

2013/8/9 Day, David <dday@redcom.com>:
> 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 ?
>

probably it is plpgsql bug, you can try FOR IN EXECUTE 'with ...'

Regards

Pavel Stehule

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