How to store record into a declared variable for later use in the query?

Question

There are 3 tables which has relations to each other so I am not able to just drop the rows.

I wrote a query which declares records, loop through 1st table then use the _id to get the record for second table then the 3rd table.

But after getting all the id from each table, I need to drop those id from 3rd table, then 2nd table then 1st table.

I am not able to declare and store the rows found into a variable.

DO $$
    DECLARE
        firstTableRecord record;
        -- tried to declare something like
        -- firstTableRecord record[]; or firstTableRecord [];

        secondTableRecord record;
        thirdTableRecord record;

    BEGIN
        -- Get FirstTable Records
        FOR firstTableRecord IN
            SELECT _id FROM public."FirstTable"
            LOOP
                RAISE NOTICE 'firstTableRecord: %', firstTableRecord;

                -- Using the Ids from FirstTable get records from SecondTable
                FOR secondTableRecord IN
                    select _id from public."SecondTable" where _id = firstTableRecord._id
                    LOOP
                        RAISE NOTICE 'secondTableRecord: %', secondTableRecord;

                        -- Using the Ids from SecondTable get records from ThirdTable
                        FOR thirdTableRecord IN
                            select _id from public."ThirdTable" where _id = firstTableRecord._id
                            LOOP
                                RAISE NOTICE 'thirdTableRecord: %', thirdTableRecord;
                            END LOOP;
                    END LOOP;
            END LOOP;

        -- remove all record found in third table
        -- remove all record found in second table
        -- remove all record found in first table

    END$$;

Am I thinking / doing this the wrong direction that there's an easier way or how can this be done?

Thanks in advance for any suggestions and advice;

are you just looking for a way to to a **"cascading delete"**? condider this previous answer: https://stackoverflow.com/a/25499662/2067753 — Paul Maxwell, Apr 22 '22 at 04:30

score 0 · Answer 1 · answered Aug 12 '22 at 14:09

I'm not a SQL expert, but after looking around for an answer I came up with using Cursors. It's a bit cumbersome and I'm not sure how performant it may be on a large recordset, but this may work:

DECLARE @MyCursor CURSOR;
DECLARE @Field1 int;
DECLARE @Field2 int;
DECLARE @Field3 int;
DECLARE @Field4 int;
DECLARE @Field5 uniqueidentifier;

BEGIN
    SET @MyCursor = CURSOR FOR
    SELECT Field1, Field2, Field3, Field4, Field5
        From <your tables with any necessary joins and or sorting>
    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor INTO 
        @Field1, @Field2, @Field3, @Field4, @Field5
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- your loop code --
        Print char(10) + N'Field1:' + Cast(@Field1 as nvarchar(50));

        FETCH NEXT FROM @MyCursor INTO 
            @Field1, @Field2, @Field3, @Field4, @Field5
    END; 
    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
End

How to store record into a declared variable for later use in the query?

1 Answers1