Olá pessoal,
Segue a função:
CREATE or REPLACE FUNCTION function_data_1()
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';
No entando, quando executada, ela apresenta o seguinte erro:
ERROR: missing FROM-clause entry for table "row"
> LINE 15: WHERE t2.file_id = row.file_id
Por que isto acontece? Como resolver?
Obrigado.
Patrick
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
