|
From: Igor Neyman [mailto:ineyman@xxxxxxxxxxxxxx]
WARNING: This email originated from outside of Perceptron! Please be mindful of PHISHING and MALWARE risks.
From: Pepe TD Vo [mailto:pepevo@xxxxxxxxx]
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 Exactly what it says in error message: you need to declare loop variable: DECLARE c record; BEGIN ……… Now, your next error will be about “execute immediate”. There is no such command in Postgres PlPgSQL. You just do “execute” for dynamic
sql. So, in short, you need to read Postgres docs to learn about the differences between Oracle’s PlSQL and Postgres PlPgSQL. Regards, Igor Neyman Oops… Wrong about “execute immediate” – it is supported. But, not wrong about reading documentation on PlPgSQL. Igor N. |
