Re: plpgsql FOR LOOP CTE problem ?

2013/8/9 Day, David <dday(at)redcom(dot)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;
>
>