Migrating plsql function to pgplsql (postgresql 9.5)

Question

I'm trying to migrate the next function into PostgreSQL:

CREATE OR REPLACE FUNCTION mar.change_const (command text) RETURNS 
VOID AS $body$
DECLARE

C_Procedure_Name   CONSTANT   varchar(30) := 'change_constraints';
V_Step                        real := 0;
/* command values: ENABLE | DISABLE */
all_constraints CURSOR(stat text) FOR
       SELECT *
       FROM   all_constraints
       WHERE  status = stat;
cons                          all_constraints%ROWTYPE;
line                          varchar(200);
cons_status                   varchar(10);
BEGIN
RAISE NOTICE 'Start : %, %', C_Procedure_Name, TO_CHAR(clock_timestamp());
V_Step := 1;
IF command IN ('enable', 'ENABLE') THEN
  cons_status := 'DISABLED';
ELSE
  cons_status := 'ENABLED';
END IF;
V_Step := 2;
FOR cons IN all_constraints(cons_status)
LOOP
    V_Step := 2.1;
    line := 'alter table ' || cons.table_name || ' ' || command ||
            ' novalidate constraint ' || CONS.CONSTRAINT_NAME;
    V_Step := 2.2;
    EXECUTE line;
END LOOP;

I know that I can switch all_constraints with information_schema.table_constraints but how can I change the other parts of the code ? I can't disable and enable constraints in PostgreSQL and I don't want to drop them in this part.

I read that I can alter the check constraints (which are the only type of constraint that I have) to novalidate but I didn't find any example in a search engine.

Use the format() function. That will at least solve the quoting problems. — wildplasser
– wildplasser, Commented Jul 10, 2017 at 20:38

Vao Tsun · Accepted Answer · 2017-07-10 20:33:59Z

1

you can set NO VALID mark for check constraint only on ADD CONSTRAINT, so you can't do it without dropping old CHECK CONSTRAINT first. Accordingly "ENABLE" here would be ALTER TABLE ...VALIDATE which would lock the table and go over all data (re)validating it.

FK constraints are the only type you can "comfortably" "disable/enable" by ALTER TABLE ... DISABLE TRIGGER ALL and then enabling them back.

But in short the procedure above is so much different from Postgres way of dealing with constraints, that I doubt you will success much here. Probably you have to save definition of all existing constraints, drop them and recreate from saved definitions to "enable"...

answered Jul 10, 2017 at 20:33

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JeyJ Over a year ago

Is there any postgresql function that get as parameter an object (constraint for example) and gives you back the create command that was used for it ?

Vao Tsun Over a year ago

for any type - no. constraints can be partially DDL'ed with select pg_get_constraintdef(oid) from pg_constraint where conname = 'b_i_fkey';

Collectives™ on Stack Overflow

Migrating plsql function to pgplsql (postgresql 9.5)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related