Migrating data from source data into new relation?

Question

I have three tables: folder, folder_group, and folder_group_permission.

These are source tables from which I need to unload data to a new schema in new tables where the logic is slightly different in the tables access, access_group, and access_group_permission.

The relations between the tables are as follows:

Table access is linked to table access_group through the ID: access.id = access_group.access_id.

Table access_group is linked to table access_group_permission through the relationship: access_group.id = access_group_permission.access_group_id.

Table folder is linked to table folder_group: folder.id = folder_group.folder_id.

Table folder_group is linked to table folder_group_permission: folder_group.id = folder_group_permission.folder_group_id.

access                                                       folder
+----+-----------------+-----------+                        +----+--------------+
| id | access_type_id  | id_entity |                        | id | folder_name  |
+----+-----------------+-----------+                        +----+--------------+
| 1  | 3               | 1         |                        | 1  | 3            |
| 2  | 3               | 2         |                        +----+-------- -----+
| 3  | 3               | 3         |                        
+----+-----------------+-----------+                        
access_group                                                 folder_group
+----+-----------------+-----------+                        +----+-----------------+-----------+
| id |     group_id    | access_id |                        | id |     group_id    | folder_id |
+----+-----------------+-----------+                        +----+-----------------+-----------+
| 1  | 2               | 1         |                        | 1  | 2               | 1         |
| 2  | 2               | 1         |                        | 2  | 2               | 1         |
| 3  | 2               | 3         |                        | 3  | 2               | 3         |
+----+-----------------+-----------+                        +----+-----------------+-----------+
access_group_permission                                      folder_group_permission
+----+-----------------+-----------------+             +----+-----------------+-----------------+
| id | access_group_id | permission_code |             | id | folder_group_id | permission_code |
+----+-----------------+-----------------+             +----+-----------------+-----------------+
| 1  | 1               | view            |             | 1  | 1               | view            |
| 2  | 1               | edit            |             | 2  | 1               | edit            |
| 3  | 2               | view            |             | 3  | 2               | view            |
+----+-----------------+-----------------+             +----+-----------------+-----------------+

Here you can see the tables. Id_entity in access table will have all folder_ids so the table can give permissions for all entites even if it is not folder. So access table has all enities and their access types, access_group table has all access_ids and group_id, access_group_permission connects into access table througth access_group table and gives permissions for each entity. And on this relation i want to input folder, folder_group and folder_group_permission.

So here is my script for the whole migration. I understand that my solution is bad, can someone help me how can i do that. I am completely confused =(. Give some ideas how it could be done

DO $$
    DECLARE
        conn text := 'dbname= host= user= password=';
        max_access_id INT;
        max_access_group_id INT;
    BEGIN
        PERFORM dblink_connect('db_connection', conn);

        SELECT COALESCE(MAX(id), 0) INTO max_access_id FROM access_control.public.access;
        SELECT COALESCE(MAX(id), 0) INTO max_access_group_id FROM access_control.public.access_group_permission;

        INSERT INTO access_control.public.access (id, access_type_id, id_entity)
        SELECT
            max_access_id + ROW_NUMBER() OVER () AS id,
            (SELECT id FROM access_type WHERE type_name = 'folder') AS access_type_id,
            f.id AS id_entity
        FROM
            dblink('db_connection',
                   'SELECT id
                    FROM folder') AS f(
                                       id BIGINT
                )
        ON CONFLICT (access_type_id, id_entity) DO NOTHING;
        
        

        INSERT INTO access_control.public.access_group (id, group_id, access_id)
        SELECT
            (SELECT COALESCE(MAX(id), 0) FROM access_control.public.access_group) + ROW_NUMBER() OVER () AS id,
            fg.group_id,
            max_access_id + ROW_NUMBER() OVER () AS access_id
        FROM
            dblink('db_connection',
                   'SELECT group_id, folder_id
                    FROM folder_group') AS fg(
                                              group_id BIGINT,
                                              folder_id BIGINT
                                              
                );

        
        INSERT INTO access_control.public.access_group_permission (id, access_group_id, permission_code)
        SELECT
            (SELECT COALESCE(MAX(id), 0) FROM access_control.public.access_group_permission) + ROW_NUMBER() OVER () AS id,
            max_access_group_id + ROW_NUMBER() OVER () AS access_group_id,
            fgp.permission_code
           
        FROM
            dblink('db_connection',
                   'SELECT folder_group_id, permission_code
                    FROM folder_group_permission') AS fgp(
                                                          folder_group_id BIGINT,
                                                          permission_code VARCHAR(128)
                )
                JOIN access_control.public.access_group ag ON ag.id = fgp.folder_group_id
        ON CONFLICT (id) DO NOTHING;

        PERFORM dblink_disconnect('db_connection');
    END $$;

Answer 1

Here is how i made it.

CREATE EXTENSION IF NOT EXISTS dblink;


INSERT into access_type(type_name, date_from, author_keycloak_uuid, update_date, update_keycloak_uuid)
VALUES ('supeditor.folder', NOW(), 'system', NOW(), 'system')
ON CONFLICT (type_name) DO NOTHING;


DO $$
    DECLARE
        conn text := 'dbname= host= user= password=';
        f_access_id INT;
        fg_access_group_id INT;
        fgp_access_group_permission_id INT;
        folder_row RECORD;
        folder_group_row RECORD;
        folder_group_permission_row RECORD;
    BEGIN
        PERFORM dblink_connect('db_connection', conn);

        FOR folder_row IN
            SELECT id, owner_keycloak_uuid, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid
            FROM dblink('db_connection',
                        'SELECT id,
                                owner_keycloak_uuid,
                                date_from,
                                date_to,
                                author_keycloak_uuid,
                                update_date,
                                update_keycloak_uuid FROM folder')
                     AS folder_remote(
                                      id INT,
                                      owner_keycloak_uuid varchar(128),
                                      date_from TIMESTAMP,
                                      date_to TIMESTAMP,
                                      author_keycloak_uuid VARCHAR(128),
                                      update_date TIMESTAMP,
                                      update_keycloak_uuid VARCHAR(128)
                    )
            LOOP
                INSERT INTO access (access_type_id, id_entity, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid, owner_keycloak_uuid)
                VALUES (
                           (SELECT id FROM access_type WHERE type_name = 'supeditor.folder'),
                           folder_row.id,
                           folder_row.date_from,
                           folder_row.date_to,
                           folder_row.author_keycloak_uuid,
                           folder_row.update_date,
                           folder_row.update_keycloak_uuid,
                           folder_row.owner_keycloak_uuid
                       )
                RETURNING id INTO f_access_id;

                RAISE NOTICE 'Inserted into access, access_id: %', f_access_id;

                FOR folder_group_row IN
                    SELECT id, folder_id, group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid
                    FROM dblink('db_connection',
                                'SELECT id,folder_id, group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid FROM folder_group WHERE folder_id = ' || quote_literal(folder_row.id))
                             AS folder_group_remote(
                                                    id BIGINT,
                                                    folder_id INT,
                                                    group_id INT,
                                                    date_from TIMESTAMP,
                                                    date_to TIMESTAMP,
                                                    author_keycloak_uuid VARCHAR(128),
                                                    update_date TIMESTAMP,
                                                    update_keycloak_uuid VARCHAR(128)
                            )
                    LOOP
                        INSERT INTO access_group (access_id, group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid)
                        VALUES (
                                   f_access_id,
                                   folder_group_row.group_id,
                                   folder_group_row.date_from,
                                   folder_group_row.date_to,
                                   folder_group_row.author_keycloak_uuid,
                                   folder_group_row.update_date,
                                   folder_group_row.update_keycloak_uuid
                               )
                        RETURNING id INTO fg_access_group_id;

                        RAISE NOTICE 'Inserted into access_group, access_group_id: %', fg_access_group_id;

                        FOR folder_group_permission_row IN
                            SELECT permission_code, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid
                            FROM dblink('db_connection',
                                        'SELECT permission_code, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid FROM folder_group_permission WHERE folder_group_id = ' || quote_literal(folder_group_row.id))
                                     AS folder_group_permission_remote(
                                                                       permission_code VARCHAR(128),
                                                                       date_from TIMESTAMP,
                                                                       date_to TIMESTAMP,
                                                                       author_keycloak_uuid VARCHAR(128),
                                                                       update_date TIMESTAMP,
                                                                       update_keycloak_uuid VARCHAR(128)
                                    )
                            LOOP
                                INSERT INTO access_group_permission (permission_code, access_group_id, date_from, date_to, author_keycloak_uuid, update_date, update_keycloak_uuid)
                                VALUES (
                                           REGEXP_REPLACE(folder_group_permission_row.permission_code, 'folder', 'access', 'g'),
                                           fg_access_group_id,
                                           folder_group_permission_row.date_from,
                                           folder_group_permission_row.date_to,
                                           folder_group_permission_row.author_keycloak_uuid,
                                           folder_group_permission_row.update_date,
                                           folder_group_permission_row.update_keycloak_uuid
                                       )
                                RETURNING id INTO fgp_access_group_permission_id;

                                RAISE NOTICE 'Inserted into access_group_permission, access_group_permission_id: %', fgp_access_group_permission_id;
                        END LOOP;
                    END LOOP;
            END LOOP;
        PERFORM dblink_disconnect('db_connection');
    END $$