plpgsql FOR LOOP CTE problem ?

From: "Day, David" <dday(at)redcom(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: plpgsql FOR LOOP CTE problem ?
Date: 2013-08-09 13:54:09
Message-ID: 401084E5E73F4241A44F3C9E6FD79428A6DE2EB7@exch-01
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2013-08-09 13:55:25 Re: Recovery.conf and PITR by recovery_target_time
Previous Message Michael Paquier 2013-08-09 13:11:28 Re: archive folder housekeeping