for loop

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have a script migrating from oracle to Postgres.  I have checked online and don't see anything wrong on for ... loop statement.  Would you please tell me what is wrong where?

CREATE OR REPLACE FUNCTION "CIDRDBA"."CIDRDBA_CONSTRAINTS" ( val in varchar(4000) ) RETURNS VOID
as $$
begin
        if val = 'DISABLE' then
                raise notice '%', 'CIDRDBA Constarints are being disabled';
                for c in ( select table_name, constraint_name from information_schema.table_constraints
                        where constraint_type = 'R' )
                loop
                        raise notice '%', 'Processing table: ' || c.table_name || ' - constraint
' || c.constraint_name;
                        execute immediate 'alter table ' || c.table_name ||' ' || val || ' constraint
 ' ||
                                c.constraint_name ;
                end loop;
                for c in ( select table_name, constraint_name from information_schema.table_constraints
                        where constraint_type = 'P' )
                loop
                        raise notice '%', 'Processing table: ' || c.table_name || ' - constraint
' || c.constraint_name;
                        execute immediate 'alter table ' || c.table_name || ' ' || val || ' constrain
t ' ||
                                c.constraint_name ;
                end loop;
        elsif val = 'ENABLE' then
                raise notice '%', 'CIDRDBA Constarints are being enabled';
                for c in ( select table_name, constraint_name from information_schema.table_constraints
                        where constraint_type = 'P' )
                loop
                        raise notice '%', 'Processing table: ' || c.table_name || ' - constraint
' || c.constraint_name;
                        execute immediate 'alter table ' || c.table_name ||' ' || val || ' constraint
 ' ||
                                c.constraint_name ;
                end loop;
                for c in ( select table_name, constraint_name from information_schema.table_constraints
                        where constraint_type = 'R' )
                loop
                        raise notice '%', 'Processing table: ' || c.table_name || ' - constraint
' || c.constraint_name;
                        execute immediate 'alter table ' || c.table_name || ' ' || val || ' constrain
t ' ||
                                c.constraint_name ;
                end loop;
        else
                raise notice '%', 'CIDRDBA nothing to do';
        end if;
end;
$$ LANGUAGE plpgsql;


ERROR:  loop variable of loop over rows must be a record or row variable or list of scalar variables
LINE 6:                 for c in ( select table_name, constraint_nam...
                            ^
SQL state: 42601
Character: 238

 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 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 to 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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux