Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Execute SQL queries stored in a table
Let's say I have a table with SQL statements in a column. I want to execute them all.
create table sql_command(
sql_text text
)
I know how to execute one SQL statement using EXEC, but I want to do it for all statements in the table.
exec (sql_command) from (select sql_text from sql_command) doesn't work.
1 answer
The following users marked this post as Works for me:
| User | Comment | Date |
|---|---|---|
| talex | (no comment) | Sep 6, 2024 at 16:53 |
I manage to solve my problem with next code:
do $$
declare
sql_text text;
sql_list text[];
begin
sql_list := array(
select
c.sql_text
from
sql_command c
);
raise notice 'start';
for sql_text in select unnest(sql_list)
loop
raise notice '%', sql_text;
execute sql_text;
end loop;
raise notice 'end';
end;
$$;
It can be simplified. I use intermediate variable sql_list because I have pretty complex select that generates sql.
Simplified version:
do $$
declare
sql_text text;
begin
for sql_text in (select c.sql_text from sql_command c)
loop
execute sql_text;
end loop;
end;
$$;
0 comment threads
1 comment thread