2013/8/9 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 ? > 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; > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
