0

I do have a SELECT ... which returns me a list SQL commands that I want to execute.

I would like to execute these in Postgres, so I do not have to pipe them again.

Here is an example, a SQL query that returns lost of SQL command to be executed for changing the owners. I do want to execute all of them.

select 'alter table '|| tablename ||' owner to jira;' from pg_tables where schemaname = 'public'
UNION
select 'alter table '|| sequence_name ||' owner to jira;' from information_schema.sequences            where sequence_schema= 'public'
UNION
select 'alter table '|| table_name ||' owner to jira;' from information_schema.views where       table_schema= 'public';

This returns something like:

alter table moved_issue_key owner to jira;
alter table jquartz_scheduler_state owner to jira;
alter table AO_60DB71_WORKINGDAYS_ID_seq owner to jira;
...

Now I do want to find a way to execute something like this in a single command. If it can be done only with SQL the better, if not PL/SQL would also do, maybe the PL/SQL could also receive parameters.

sorin
  • 161,544
  • 178
  • 535
  • 806
  • It's not that, one let me put an code example. – sorin Apr 25 '14 at 09:05
  • 1
    Sounds much the same to me - just `FOR var IN SELECT ... LOOP` over the rows produced by your SQL and `EXECUTE` each row. There's absolutely no way to do this without PL/PgSQL's `EXECUTE`, going via the client, or using some other PL. You'll need a PL/PgSQL function or DO block. – Craig Ringer Apr 25 '14 at 09:23
  • 1
    If you really *must* avoid plpgpsql, the only way is to pipe the script throught psql. (or build an embedded-SQL program which basically juist does the same) The advantage of piping through psql is that you can inspect the output. (and the input script before/after submitting it) – joop Apr 25 '14 at 12:11

0 Answers0