0

I'm trying to port over some statements from Oracle to Postgres.

One specific one that I have a hard time even understanding on the Oracle side is this:

rating_scheme_oid NUMBER;
rs_cnt number;

From what I can understand it seems like these are just defining variables, so I figured I could translate that to this:

CREATE TYPE rating_scheme_oid NUMERIC;
CREATE TYPE rs_cnt NUMERIC;

However this doesn't seem to work: ERROR: syntax error at or near "NUMERIC"

If anyone with expertise in this area could describe what these are doing and a possible solution, that'd be great.

Here's the full context (this is the working Oracle statement):

        DECLARE
        CURSOR update_cursor
        IS
        SELECT oid, subscription_id, configuration_oid
        FROM scope
        WHERE subscription_id != 1 and subclass_type = 'W' and configuration_oid is not null;

        TYPE type
        IS
        TABLE OF update_cursor%ROWTYPE;
        update_array type;

        rating_scheme_oid NUMBER;
        rs_cnt number;

        BEGIN
        OPEN update_cursor;
        LOOP
        FETCH update_cursor BULK COLLECT INTO update_array LIMIT 1000;
        FOR i IN 1 .. update_array.COUNT LOOP

        BEGIN
        select count(*) into rs_cnt from workspace_config where workspace_oid = update_array(i).oid and OBJECTIVE_STATE_SCHEME_OID is null;
        IF rs_cnt = 1
        THEN

        SELECT OID_SEQ.nextval into rating_scheme_oid from DUAL;

        INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
        VALUES (rating_scheme_oid, update_array(i).subscription_id, 1, null, SYSDATE, update_array(i).oid, sys_guid());

        UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = update_array(i).configuration_oid;

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Defined', 0, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Committed', 1, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'In-Progress', 2, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Measuring', 3, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Achieved', 4, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Closed', 5, sys_guid());

        END IF;

        END;
        END LOOP;
        COMMIT;
        EXIT WHEN update_cursor%NOTFOUND;
        END LOOP;
        CLOSE update_cursor;
        END;
Improve this question
0

1 Answer 1

Reset to default
3

A direct replacement of the code to PL/pgSQL would be something like this.

The following is (obviously not) tested and may contain typos, so you need to work out the details from here. But it should get you started.

DECLARE
  l_oid_array int[];
  l_subscription_id_array int[];
  l_config_oid_array int[];

  l_rating_scheme_oid int;  
  l_cnt bigint;  
BEGIN
  SELECT array_agg(oid order by oid), 
         array_agg(subscription_id order by oid), 
         array_agg(configuration_oid order by oid)
     into l_oid_array, l_subscription_id_array, l_config_oid_array
  FROM scope
  WHERE subscription_id <> 1 
    and subclass_type = 'W' 
    and configuration_oid is not null;
    
  FOR i IN 1 .. cardinality(l_oid_array) LOOP
  
    select count(*) into l_cnt
    from workspace_config 
    where workspace_oid = workspace_oid = l_oid_array[i] 
      and OBJECTIVE_STATE_SCHEME_OID is null;
      
    IF l_cnt = 1 THEN
    THEN

      l_rating_scheme_oid := nextval('oid_seq');

      INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
      VALUES (rating_scheme_oid, l_subscription_id_array[i], 1, null, current_date, l_oid_array[i], uuid_generate_v4());

      UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = l_config_oid_array[i];

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Defined', 0, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Committed', 1, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'In-Progress', 2, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Measuring', 3, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Achieved', 4, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Closed', 5, uuid_generate_v4());
    END IF;
    
  END LOOP;

END;

To get the uuid_generate_v4() function you need to install the uuid-ossp extension

Doing things in loops like that is usually the least efficient way (in Oracle and Postgres). More often than not, a simple insert into select ... is a lot more efficient

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.