Re: plpgsql FOR LOOP CTE problem ?

A complete self contained test case: example of the problem with my FOR LOOP using a COMMON table expression.
Again this is version 9.3beta

Any comments

Thanks

CREATE SCHEMA test
CREATE TABLE test.tmm
(
name character varying,
tu_id integer NOT NULL DEFAULT 1,
translator_id integer NOT NULL,
tid_seq integer NOT NULL,
ws_grp_seq integer DEFAULT 0,
obj_seq integer NOT NULL,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer NOT NULL DEFAULT 0,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
-- row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
CONSTRAINT translator_member_pkey PRIMARY KEY (translator_id, tu_id, obj_seq)
-- CONSTRAINT translator_member_call_priority_fkey FOREIGN KEY (call_priority__level)
-- REFERENCES admin.call_priority_level_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_entry_type_fkey FOREIGN KEY (entry_type)
-- REFERENCES admin.translator_entry_type_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_owner_port_id_fkey FOREIGN KEY (owner_port_id)
-- REFERENCES admin.port (port_id) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_pattern_match_fkey FOREIGN KEY (pattern_match)
-- REFERENCES admin.translator_pattern_match_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_trans_result_fkey FOREIGN KEY (trans_result)
-- REFERENCES admin.translator_result_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);

CREATE TABLE test.tum
(
name character varying,
tu_id integer NOT NULL DEFAULT 1,
translator_id integer,
tid_seq integer,
ws_grp_seq integer DEFAULT 0,
obj_seq integer NOT NULL,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
pattern_id integer,
pattern_class_id integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer NOT NULL DEFAULT 0,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
-- row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
CONSTRAINT translator_user_mbr_pkey PRIMARY KEY (tu_id, obj_seq)
-- CONSTRAINT translator_mbr_owner_port_id_fkey FOREIGN KEY (owner_port_id)
-- REFERENCES admin.port (port_id) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_mbr_pattern_match_fkey FOREIGN KEY (pattern_match)
-- REFERENCES admin.translator_pattern_match_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_entry_type_fkey FOREIGN KEY (entry_type)
-- REFERENCES admin.translator_entry_type_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_mbr_call_priority_fkey FOREIGN KEY (call_priority__level)
-- REFERENCES admin.call_priority_level_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_mbr_trans_result_fkey FOREIGN KEY (trans_result)
-- REFERENCES admin.translator_result_enum (value) MATCH Unknown1,
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_mbr_tu_id_fkey FOREIGN KEY (tu_id)
-- REFERENCES admin.translator_user (tu_id) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
OIDS=FALSE
);

INSERT INTO test.tmm ( translator_id, tid_seq, tu_id, obj_seq) VALUES
(1,1,1,1),
(1,1,1,2),
(1,1,1,3),
(1,1,1,4),
(2,1,1,1),
(2,1,1,2),
(2,1,1,3);

INSERT INTO test.tum ( translator_id, tu_id, obj_seq) VALUES
(1,1,1),
(1,1,2),
(1,1,3),
(1,1,4),
(2,2,1),
(2,2,2),
(2,2,3);

CREATE OR REPLACE FUNCTION test.activate_translator_user (ws_id integer)
RETURNS void AS
$BODY$

DECLARE
drow test.tmm%ROWTYPE; -- deleted row holder
wsrow test.tum%ROWTYPE;
patt CHARACTER VARYING;

BEGIN
-- Remove current subsection of the translator but grab some
-- sequenceing information from it.
-- ( Presumes that this is an update !!!!!! )

FOR drow IN
WITH xrows AS (
DELETE FROM test.tmm
WHERE tu_id = ws_id RETURNING *
)
SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
FROM xrows GROUP BY translator_id
LOOP


Raise notice ' INPUT % TID_seq % WS_seq % TID % ', ws_id, drow.tid_seq, drow.ws_grp_seq, drow.translator_id;

-- Replace the removed subsection of relevant translation sets.
FOR wsrow IN
SELECT * FROM test.tum a
WHERE a.tu_id = ws_id
ORDER BY obj_seq ASC
LOOP

INSERT INTO test.tmm (
"tu_id", "translator_id",
"tid_seq", "ws_grp_seq", "obj_seq",
"entry_type", "pattern_key", "pattern",
"pattern_match", "screen_class", "term_mode",
"trans_result", "port_id", "tag_id",
"cause__q850", "cause__redcom", "new_translator_id",
"new_screen_class", "duration", "call_priority__level",
"address", "macro_type", "owner_usr_id",
"owner_port_id", "sys_note", "continue_translating",
"user_message", "label", "label_reference"
) VALUES (
wsrow."tu_id", drow."translator_id",
drow."tid_seq", drow."ws_grp_seq", wsrow."obj_seq",
wsrow."entry_type", wsrow."pattern_key", patt,
wsrow."pattern_match", wsrow."screen_class", wsrow."term_mode",
wsrow."trans_result", wsrow."port_id", wsrow."tag_id",
wsrow."cause__q850", wsrow."cause__redcom", wsrow."new_translator_id",
wsrow."new_screen_class", wsrow."duration", wsrow."call_priority__level",
wsrow."address", NULL, wsrow."owner_usr_id",
wsrow."owner_port_id", wsrow."sys_note", wsrow."continue_translating",
wsrow."user_message", wsrow."label", wsrow."label_reference" );
END LOOP; -- wsrow
END LOOP; -- drow

END
$BODY$
LANGUAGE plpgsql VOLATILE;

--- Here is executing the function and bad result

select * from test.activate_translator_user(1);
NOTICE: INPUT 1 TID_seq <NULL> WS_seq <NULL> TID 0
ERROR: null value in column "tid_seq" violates not-null constraint
DETAIL: Failing row contains (null, 1, 0, null, null, 1, 0, null, null, 0, 0, null, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null).
CONTEXT: SQL statement "INSERT INTO test.tmm (
"tu_id", "translator_id",
"tid_seq", "ws_grp_seq", "obj_seq",
"entry_type", "pattern_key", "pattern",
"pattern_match", "screen_class", "term_mode",
"trans_result", "port_id", "tag_id",
"cause__q850", "cause__redcom", "new_translator_id",
"new_screen_class", "duration", "call_priority__level",
"address", "macro_type", "owner_usr_id",
"owner_port_id", "sys_note", "continue_translating",
"user_message", "label", "label_reference"
) VALUES (
wsrow."tu_id", drow."translator_id",
drow."tid_seq", drow."ws_grp_seq", wsrow."obj_seq",
wsrow."entry_type", wsrow."pattern_key", patt,
wsrow."pattern_match", wsrow."screen_class", wsrow."term_mode",
wsrow."trans_result", wsrow."port_id", wsrow."tag_id",
wsrow."cause__q850", wsrow."cause__redcom", wsrow."new_translator_id",
wsrow."new_screen_class", wsrow."duration", wsrow."call_priority__level",
wsrow."address", NULL, wsrow."owner_usr_id",
wsrow."owner_port_id", wsrow."sys_note", wsrow."continue_translating",
wsrow."user_message", wsrow."label", wsrow."label_reference" )"
PL/pgSQL function test.activate_translator_user(integer) line 32 at SQL statement

-- ? why does the CTE from the command line get the right result ?

ace_db=# with xrows as(
ace_db(# select * from test.tmm where tu_id = 1
ace_db(# )
ace_db-# select translator_id, min(tid_seq), min(ws_grp_seq) from xrows group by translator_id;
translator_id | min | min
---------------+-----+-----
1 | 1 | 0
2 | 1 | 0
(2 rows)

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