Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKSer-0007ez-Fz for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 18:34:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gKSep-0000Fb-Pp for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 18:34:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKSep-0000FU-FP for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 18:34:27 +0000 Received: from sonic316-13.consmr.mail.bf2.yahoo.com ([74.6.130.123]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKSei-0005Mn-Oh for pgsql-admin@postgresql.org; Wed, 07 Nov 2018 18:34:27 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1541615658; bh=XH8sufLpqXB8EHNPJ2R/nbtUeRjK/2BwfRcCw/sumaM=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=HrrNcr2RRa9wj1g+PB+DkFi6ZVELymGoVFjDIVkPJYoTAJbIMa7s3lUSl5xYvh28fNZEfXVz1L5iSUgEwnn5sqf6xPK3yvZ7rgjnCkd9oAPA80TJCKzL2GgBExkH6gMPuOpaViz4XFnfpHPcrTwazsacl8FeHhzmq8yTArkhYcU8UC5lTaNBci0+rqYCw0wB5YwMAQQdc2qwMjYHlovZxUwsereWF5DcdUADAsLSI0iTenwR6aLKqX9Vc8cMeQUDuBFHWypN3JHagjvYWPRtD3l0VkfhBoJS1o74SOGP+SPVU+OnglxK6SdzA+hPHFBh40q5IC0orNQgOEM0E9JdMg== X-YMail-OSG: gkdMX6oVM1lVjgiwEBtV4SWrRknaNol6VMfmFRVdHHKtBGTAbqYlOnGJ1uRiL8N MhxSU80WNMdgdTmshR2U7lvFzZ5qFD7hKv5jeyHDUad4TIRMgSamQ3qXBFIqDci7sduNIS4K4d4i AiB6VwqleAuiYBXn5OIe_SWkuB8_kLJLQdPq0cUitI.JCvcAoaO2K9x2gNQmc5aqtHahX4AabItm Q8xRI58gI9sx.s6T2jhDBQwf4tDoJ.SkAQpdJlPIhrpIVB2BcE2OVSAfGd_fZDHcCSvXM_ecfUkC xebZt74T_eQliolGpdLPzOkVE39Abihn1XIKYr4FXE.yJ_4_HhWuchSrilhHu0vtiH3QtAS1yl8G bHNm.N3raWrO9.GaqFddiG6uvkqvHIWjMgHPvxTVxxx_lBinKTlLLGa.FzCZTxJI0v6GLKKuIrVf rLEf4O6CQGvT0h9xSUUtZQWIsNonJwQhGhzLE0Ef3YG9dtOPYQFmaiCBJn._ln8zpQ9zC8OGk6s0 WNpbNdrt7AXzCx3PI6mwcgvOa3m4Tg4AMovojafr5VmYlRSmZcACgUXTf0Z3Wy3vCquqOOG.9LN3 ZeAB_ZhmdAh67VBeZG3RreWbHK0LDMg4yDitqS1896fcTIst9YsOS.Hf2lmyblbtcJCiWZ4l0E2i OQzcLiSGOM6sFMlz9Y6f.6pgh_HoFgF8dyRTShjYvHjEJi5.7ZsZYKPyE317RkFlZ8o4AfeNovIx yl.DLn_28tuLRPhh7GKxUE3lGV8IQ8J0kqcxDObzmtpulOeWap0ogxSHBazj.0lc_afyPuCOVRMr xbVrkgsK2sUfkITuLJALHqAY45dbogHMXPIgjUEQ0kUkfxpEZ8O2ymPiCaq6D7KQcmnXF1_aYdq7 yJLX.vxFOCsUZeoaBlukxkWObs_P5F2B7kgqFaw63Q0WLFE7glBUfd4gv_JEmeZrfN9QISuAAhNY cDQampYLl2zegJNWeRwe8YnBQSL11P9BwJY_CGyBwOu_nQU22lFeCllR.lMr4pg-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.bf2.yahoo.com with HTTP; Wed, 7 Nov 2018 18:34:18 +0000 Date: Wed, 7 Nov 2018 18:34:11 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin Message-ID: <1261661192.463274.1541615651596@mail.yahoo.com> Subject: for loop MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_463273_1421104479.1541615651593" References: <1261661192.463274.1541615651596.ref@mail.yahoo.com> X-Mailer: WebService/1.1.12710 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36 Content-Length: 16125 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_463273_1421104479.1541615651593 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I have a script migrating from oracle to Postgres.=C2=A0 I have checked onl= ine and don't see anything wrong on for ... loop statement.=C2=A0 Would you= please tell me what is wrong where? CREATE OR REPLACE FUNCTION "CIDRDBA"."CIDRDBA_CONSTRAINTS" ( val in varchar= (4000) ) RETURNS VOIDas $$begin=C2=A0 =C2=A0 =C2=A0 =C2=A0 if val =3D 'DISA= BLE' then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 raise noti= ce '%', 'CIDRDBA Constarints are being disabled';=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 for c in ( select table_name, constraint_na= me from information_schema.table_constraints=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 where constraint_type = =3D 'R' )=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 loop=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 raise notice '%', 'Processing table: ' || c.table_name || ' - constrain= t' || c.constraint_name;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 execute immediate 'alter table ' || c.ta= ble_name ||' ' || val || ' constraint=C2=A0' ||=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 c.constraint_name ;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 end loop;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= for c in ( select table_name, constraint_name from information_schema.tabl= e_constraints=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 where constraint_type =3D 'P' )=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 loop=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 raise notice '%', 'Process= ing table: ' || c.table_name || ' - constraint' || c.constraint_name;=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 execute immediate 'alter table ' || c.table_name || ' ' || val || ' con= straint ' ||=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 c.constraint_name ;=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 end loop;=C2=A0 =C2=A0 =C2= =A0 =C2=A0 elsif val =3D 'ENABLE' then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 raise notice '%', 'CIDRDBA Constarints are being enabl= ed';=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 for c in ( sele= ct table_name, constraint_name from information_schema.table_constraints=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 where constraint_type =3D 'P' )=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 loop=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 raise notice '%', 'Processing table: ' |= | c.table_name || ' - constraint' || c.constraint_name;=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 execute imm= ediate 'alter table ' || c.table_name ||' ' || val || ' constraint=C2=A0' |= |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 c.constraint_name ;=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 end loop;=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 for c in ( select table_name, constraint_name = from information_schema.table_constraints=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 where constraint_type =3D= 'R' )=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 loop=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 raise notice '%', 'Processing table: ' || c.table_name || ' - constrain= t' || c.constraint_name;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 execute immediate 'alter table ' || c.ta= ble_name || ' ' || val || ' constraint ' ||=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 c.constraint_name ;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 end loop;=C2=A0 =C2=A0 =C2=A0 =C2=A0 else=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 raise notice '%', 'CIDRDBA nothing to do';=C2= =A0 =C2=A0 =C2=A0 =C2=A0 end if;end;$$ LANGUAGE plpgsql; ERROR:=C2=A0 loop variable of loop over rows must be a record or row variab= le or list of scalar variablesLINE 6:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0for c in ( select table_name, constraint_nam...=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ^SQL state: 42601Character: 238 =C2=A0Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir mistakes you will be alone. So judge less, love and forgive more.To call= him a dog hardly seems to do him justice though in as much as he had four = legs, a tail, and barked, I admit he was, to all outward appearances. But t= o those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely = rich *** Faithful talent *** Sharing success ------=_Part_463273_1421104479.1541615651593 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I have a script migrating from oracle to Postgres.&nbs= p; I have checked online and don't see anything wrong on for ... loop state= ment.  Would you please tell me what is wrong where?

CREATE OR REPLACE FUNCTION "CIDR= DBA"."CIDRDBA_CONSTRAINTS" ( val in varchar(4000) ) RETURNS VOID
as $$
begin
        if val =3D 'DISABLE' then
      &nb= sp;         raise notice '%', 'CIDRDBA Constarints are = being disabled';
&nb= sp;               for c in ( select tabl= e_name, constraint_name from information_schema.table_constraints
        &= nbsp;               where constraint_typ= e =3D 'R' )
  &= nbsp;             loop
            =             raise notice '%', 'Processing tab= le: ' || c.table_name || ' - constraint
' || c.constraint_name;
              &n= bsp;         execute immediate 'alter table ' || c.tabl= e_name ||' ' || val || ' constraint
 ' ||
                   = ;             c.constraint_name ;
        &nb= sp;       end loop;
                for = c in ( select table_name, constraint_name from information_schema.table_con= straints
  &nbs= p;                     wh= ere constraint_type =3D 'P' )
                loop
      &nb= sp;                 raise notice '%= ', 'Processing table: ' || c.table_name || ' - constraint
' || c.constraint_name;
        &nbs= p;               execute immediate 'alte= r table ' || c.table_name || ' ' || val || ' constrain
t ' ||
              &nbs= p;                 c.constraint_nam= e ;
    &n= bsp;           end loop;
        elsif val =3D 'EN= ABLE' then
  &n= bsp;             raise notice '%', 'CIDRDBA C= onstarints are being enabled';
                for c in= ( select table_name, constraint_name from information_schema.table_constra= ints
    &= nbsp;                   where = constraint_type =3D 'P' )
                loop
        =                 raise notice '%', '= Processing table: ' || c.table_name || ' - constraint
' || c.constraint_name;
          &n= bsp;             execute immediate 'alter tab= le ' || c.table_name ||' ' || val || ' constraint
 ' ||
               = ;                 c.constraint_name= ;
    &nb= sp;           end loop;
            &nbs= p;   for c in ( select table_name, constraint_name from information_sc= hema.table_constraints
                    &n= bsp;   where constraint_type =3D 'R' )
              &n= bsp; loop
  &nb= sp;                     r= aise notice '%', 'Processing table: ' || c.table_name || ' - constraint
' || c.constraint_name;=
     = ;                   execute im= mediate 'alter table ' || c.table_name || ' ' || val || ' constrain
t ' ||
           = ;                     c.c= onstraint_name ;
&nb= sp;               end loop;
        else=
     = ;           raise notice '%', 'CIDRDBA nothing to = do';
    &= nbsp;   end if;
end;
$$ LANGUAGE pl= pgsql;


ERROR:  loop variable of loop over rows must be a r= ecord or row variable or list of scalar variables
LINE 6:      &nbs= p;          for c in ( select table_name, constrai= nt_nam...
                    &nb= sp;       ^
SQL state: 42601
Character: 238

 
<= span class=3D"yiv3811552299sg">
Bach-Nga

No one in this world i= s pure and perfect.  If you avoid people for their mistakes you will b= e alone. So judge less, love and forgive more.
To = call him a dog hardly seems to do him justice though in as much as he had f= our legs, a tail, and barked, I admit he was, to all outward appearances. B= ut to those who knew him well, he was a perfect gentleman (Hermione Gingold= )

**Live simply **Lov= e generously **Care deeply **Speak kindly.
*** Genuinely r= ich *** Faithful talent *** Sharing success
= ------=_Part_463273_1421104479.1541615651593--