pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedfor loop
3+ messages / 2 participants
[nested] [flat]
* for loop
@ 2018-11-07 18:34 Pepe TD Vo <pepevo@yahoo.com>
2018-11-07 18:46 ` RE: for loop Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pepe TD Vo @ 2018-11-07 18:34 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: for loop
2018-11-07 18:34 for loop Pepe TD Vo <pepevo@yahoo.com>
@ 2018-11-07 18:46 ` Igor Neyman <ineyman@perceptron.com>
2018-11-07 19:31 ` RE: for loop Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 3+ messages in thread
From: Igor Neyman @ 2018-11-07 18:46 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin
From: Pepe TD Vo [mailto:pepevo@yahoo.com]
Sent: Wednesday, November 07, 2018 1:34 PM
To: Pgsql-admin <pgsql-admin@postgresql.org>
Subject: for loop
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: for loop
2018-11-07 18:34 for loop Pepe TD Vo <pepevo@yahoo.com>
2018-11-07 18:46 ` RE: for loop Igor Neyman <ineyman@perceptron.com>
@ 2018-11-07 19:31 ` Igor Neyman <ineyman@perceptron.com>
0 siblings, 0 replies; 3+ messages in thread
From: Igor Neyman @ 2018-11-07 19:31 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin
From: Igor Neyman [mailto:ineyman@perceptron.com]
Sent: Wednesday, November 07, 2018 1:47 PM
To: Pepe TD Vo <pepevo@yahoo.com>; Pgsql-admin <pgsql-admin@postgresql.org>
Subject: RE: for loop
WARNING: This email originated from outside of Perceptron! Please be mindful of PHISHING and MALWARE risks.
From: Pepe TD Vo [mailto:pepevo@yahoo.com]
Sent: Wednesday, November 07, 2018 1:34 PM
To: Pgsql-admin <pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>>
Subject: for loop
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-11-07 19:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-07 18:34 for loop Pepe TD Vo <pepevo@yahoo.com>
2018-11-07 18:46 ` Igor Neyman <ineyman@perceptron.com>
2018-11-07 19:31 ` Igor Neyman <ineyman@perceptron.com>
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