Delete from a dynamic table name in postgres

Question

I have this structure of table, let's call person

id  | name  | table_name  |oid
----+-------+-------------+---
 1  | James | customer    | 5
 2  | Smith | employe     | 6

So what I want is to create a SQL script that allows me delete the row with id = 5 in the customer table before deleting the first row in the person table.

This is the first time I am facing this situation, so I have no idea how to do this. I've done several searches on Google but I haven't get any appropriate answer, could anyone help me?

Are you really still using Postgres 9.1? That has bee out of support for 2 years now. You should plan an upgrade to a supported version as soon as possible — user330315
– user330315, Commented Sep 25, 2018 at 5:41

Kaushik Nayak · Accepted Answer · 2018-09-25 03:38:17Z

2

Use a cursor for loop to traverse your table and run a dynamic delete. You may then delete your person table.

DO $$
DECLARE
v_rec RECORD;
BEGIN
for v_rec IN ( select table_name, oid from person )
LOOP
  EXECUTE format('DELETE FROM '||v_rec.table_name ||' WHERE id = $1' )  USING v_rec.oid;
END LOOP;
END;
$$ LANGUAGE plpgsql;

I would suggest you to change this design if possible.

answered Sep 25, 2018 at 3:38

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete from a dynamic table name in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related