Re: plpgsql FOR LOOP CTE problem ?

Hi Tom,

That's a good thought on the duplicated variable names.
The two table involved do have many common column names
But the inputs are explicitly referencing tables on the inserts.
Would not seem to be possible to confuse them.

In trying to make a simplified test case, I came across
Something else I thought odd.

My observation is that if the inner loop references the
"drow" LOOP variable the actual deletion in table aaa by the outer loop
is not taking place as I would hope.
RE: the inner loop insert aaa table fails.
I get a duplicate primary key violation on the insert in my sample function below.

So far, for my test case below, I do not see the original reported error where the MINed
Column value is NULL. Still trying to replicate that in a simplified version.

The purpose of the function is to replace in a master table (aaa) a subsection that
A user has modify privileges on within a separate work space table (bbb) .

I am continuing to explore replicating the original problem. Thought this tangent
might suggest something to you. ( hopefully that I'm not a total idiot )

Thanks

Dave

CREATE TABLE admin.aaa (
translator_id integer,
tid_seq integer NOT NULL,
ws_grp_seq integer,
PRIMARY KEY (translator_id, tid_seq)
);

INSERT INTO admin.aaa (translator_id,tid_seq, ws_grp_seq ) VALUES
(1,1,1),
(1,2,1),
(1,3,1),
(2,1,1),
(2,2,1),
(2,3,1);

CREATE TABLE admin.bbb (
translator_id integer,
tid_seq integer,
ws_grp_seq integer,
PRIMARY KEY (translator_id, tid_seq)
);

INSERT INTO admin.bbb (translator_id,tid_seq, ws_grp_seq ) VALUES
(1,1,1),
(1,2,1),
(1,3,1);

CREATE OR REPLACE FUNCTION admin.activate_translator_user1 (ws_id integer)
RETURNS void AS
$BODY$

DECLARE
drow admin.aaa%ROWTYPE; -- deleted row holder
nrow admin.bbb%ROWTYPE; --

BEGIN
-- Remove current subsection of the translator but grab some
-- sequenceing information from it.


FOR drow IN
WITH xrows AS (
DELETE FROM admin.aaa
WHERE translator_id = ws_id RETURNING *
)
SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
FROM xrows GROUP BY translator_id
LOOP
Raise notice ' TID_seq % WS_seq % TID % ', drow.tid_seq, drow.ws_grp_seq, drow.translator_id; -- output is correct( all 3 cases ? )
FOR nrow IN
SELECT * FROM admin.bbb WHERE translator_id = ws_id
LOOP
INSERT INTO admin.aaa ( translator_id, tid_seq, ws_grp_seq ) VALUES
( nrow.translator_id, nrow.tid_seq, nrow.tid_seq ); -- works correctly.
-- ( nrow.translator_id, drow.tid_seq, nrow.tid_seq ); -- duplicate primary key error.
-- ( drow.translator_id, drow.tid_seq, drow.tid_seq ); -- duplicate prinary key errors -

END LOOP; -- drow
END LOOP; -- drow

END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

select admin.activate_translate_user1(1);

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, August 09, 2013 10:20 AM
To: Day, David
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

"Day, David" <dday(at)redcom(dot)com> writes:
> 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 ????

Perhaps those column names duplicate variable names within the function, and what's being returned is the values of the variables?

If you think there's a bug here, you'd need to provide a self-contained test case.

regards, tom lane