如何在循环数据中删除数据
请有人帮助您做到这一点。 我有一个日志表，如下所示，我将在3个月内删除所有桌子。 现在，我想在采取上述步骤时添加另一个步骤以清除日志表。示例：如果＆gt放置表格；然后，90天也从日志表中删除关联的行。
谢谢
ddl_date ddl_tag id object_name 2022-07-07 16:40:06创建外国1 raj.auth 2022-02-07 17:14:33创建表6 John.plots_source 2022-03-07 17:14:33创建表7 John.plots1 2022-04-07 17:14:33创建表8 johnb.plots_pkey2 2022-05-07 17:14:33创建表9 johna.plots_address3
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN
(
SELECT id, object_name from user_monitor.ddl_history WHERE ddl_date < NOW() - INTERVAL '90 days'
)
LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || r.object_name || ' CASCADE';
END LOOP;
delete from user_monitor.ddl_history where not exists (select tablename from pg_catalog.pg_tables b where r.object_name = b.tablename);
commit;
END $$ ;
SQL错误[42601]：错误：查询没有结果数据的目的地
提示：如果要丢弃选择的结果，请使用persim 反而。地点：pl/pgsql函数inline_code_block line 12 at sql 语句
Please can someone help on how to do this.
I have a log table as below where i am deleting all tables beyond 3 months.
Now i want add another step to clear the log table as well when the above step has been actioned. Example: drop the table if > 90 days then delete the associated rows from the log table too.
thanks
ddl_date ddl_tag ID object_name 2022-07-07 16:40:06 CREATE FOREIGN 1 raj.auth 2022-02-07 17:14:33 CREATE TABLE 6 john.plots_source 2022-03-07 17:14:33 CREATE TABLE 7 john.plots1 2022-04-07 17:14:33 CREATE TABLE 8 johnb.plots_pkey2 2022-05-07 17:14:33 CREATE TABLE 9 johna.plots_address3
DO $
DECLARE
r RECORD;
BEGIN
FOR r IN
(
SELECT id, object_name from user_monitor.ddl_history WHERE ddl_date < NOW() - INTERVAL '90 days'
)
LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || r.object_name || ' CASCADE';
END LOOP;
delete from user_monitor.ddl_history where not exists (select tablename from pg_catalog.pg_tables b where r.object_name = b.tablename);
commit;
END $ ;
SQL Error [42601]: ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM
instead. Where: PL/pgSQL function inline_code_block line 12 at SQL
statement
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您是在循环外引用R.Object_name。
尝试将删除语句在循环内部拉动，以便“ r”是一个有效的参考，可以在执行上下文中所有光标值之间循环。
You're referencing r.object_name outside of the LOOP.
Try pulling the DELETE statement inside the LOOP so that "r" is a valid reference that can cycle among all cursor values within the execution context.