Re: plpgsql FOR LOOP CTE problem ? - Mailing list pgsql-general

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@sss.pgh.pa.us]
Sent: Friday, August 09, 2013 10:20 AM
To: Day, David
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

"Day, David" <dday@redcom.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