1

I'm working on a PostgreSQL stored procedure to remove records from a set of tables. The criteria for removal is the same on all the tables. So I've got something like this:

-- Start with the accounts table
DELETE 
FROM accounts
WHERE account_id=account_id_to_be_deleted

-- Next the other tables
DELETE
FROM assets
WHERE account_id=account_id_to_be_deleted

for about 11 tables. account_id_to_be_deleted is a parameter to the SP as you might guess. All 11 tables have an account_id field in them. What I want is to be able to parameterize the delete query on the table name. Something along the lines of this pseudo-code:

set list of table names

table_names=(accounts, assets ...)

for each table in table_names do

{

DELETE
FROM table
WHERE account_id=account_id_to_be_deleted

}

Is this possible? My google skills don't seem to be up to the task of finding an answer on this one. If this has been asked and answered (I cannot believe I'd be the first developer to want this ability) feel free to point me to the answer and I'll close this question.

2
  • The search term you're looking for is "dynamic SQL".
    – mustaccio
    Dec 17, 2021 at 16:05
  • @mustaccio--that may be the answer for my question. I'll have to try it out and see if I can make it work. Dec 17, 2021 at 17:02

0

Browse other questions tagged or ask your own question.