I did:

CREATE or REPLACE FUNCTION function_1_data()

RETURNS INTEGER AS $$


declare

        row record;


BEGIN


-- copying the data to the backup table (not the blobs)

FOR row IN EXECUTE '

            SELECT

                    t1.file_id

            FROM

                    table1_n_b t1

            JOIN

                    table3_n_b t3 ON t3.file_id = t1. file_id ORDER BY 1
LIMIT 3' LOOP


-- Creating the backup table with the essential data

EXECUTE '

INSERT INTO table2_y_b (note_id, size, file_id, full_path)

    (

            SELECT

                    t1.note_id,

                    t1.size,

                    t1.file_id,

                    t1.full_path

            FROM

                    table1_n_b t1

            JOIN

                    table3_n_b t3 ON t3.file_id = t1.file_id

    ) ';


-- copying the blobs to the table above

EXECUTE '

        UPDATE table2_y_b t2 SET data =

        (

            SELECT

                    o1.data

            FROM

                    original_table1_b o1

            JOIN

                    table3_n_b t3 ON t3.file_id = o1.file_id

            WHERE

                    t3.migrated = 0

            AND

                    t2.file_id = o1.file_id

        )

        WHERE t2.file_id = row.file_id ';



-- updating the migrated column from 0 to 1

EXECUTE '

        UPDATE

                table2_y_b t2

        SET

                migrated = 1

        WHERE

                t2.file_id = row.file_id

        AND

                migrated = 0 ';



-- setting the blob as null

EXECUTE '

        UPDATE

                original_table1_b o1

        SET

                data = NULL

        WHERE

                o1.file_id = row.file_id ';

END LOOP;


return row.file_id;


END


$$ language 'plpgsql';



*And I'm getting the error:*

> missing FROM-clause entry for table "row"
> WHERE t2.st_ino = row.st_ino


Why does that happen?

Reply via email to