From: Pepe TD Vo <pepevo@yahoo.com>
To: Pgsql-admin <pgsql-admin@postgresql.org>
Subject: for loop
Date: Wed, 7 Nov 2018 18:34:11 +0000 (UTC)
Message-ID: <1261661192.463274.1541615651596@mail.yahoo.com> (raw)
References: <1261661192.463274.1541615651596.ref@mail.yahoo.com>
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 VOIDas $$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 || ' constraint ' || 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 || ' constraint ' || 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 variablesLINE 6: for c in ( select table_name, constraint_nam... ^SQL state: 42601Character: 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com
Subject: Re: for loop
In-Reply-To: <1261661192.463274.1541615651596@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox