0

I'm open to other suggestions on how to do this. The goal is that when I commit my code to the repo and push to Prod it will run all my "create or replace" against my production DB.

I've written a release script that runs run_all_procedures() and inside that function, I want to have a bunch of commands that will run a bunch of other functions. So I guess I want to do something like this:

 Create Or Replace Function run_all_procedures() Returns void As $$ 
 Begin
    psql -f my_dir/do_something.sql
    psql -f my_dir/do_something_else.sql
    psql -f my_dir/do_a_final_thing.sql         
 End;
 $$ language plpgsql;

Of course, this doesn't work.

Improve this question
0

1 Answer 1

Reset to default
2

Start to run this simple test as superuser:

  Create table cmd_result (str text); 

  Create Or Replace Function run_all_procedures() Returns void As $$ 
     Begin
     copy cmd_result from program 'psql postgres -c "select * from pg_stat_activity;" ';    
     End;
   $$ language plpgsql;

  select run_all_procedures();
  select * from cmd_result;

Maybe you will need to specify full path to get results from psql...

Now the function...

You must guarantee that postgres unix/windows-user have permission to read do_something.sql then just do: 

 create table cmd_result (str text); 

 Create Or Replace Function run_all_procedures() Returns void As $$ 
 Begin
    copy cmd_result from program 'psql  -f /fullpath/do_something.sql" ';
    copy cmd_result from program 'psql  -f /fullpath/do_something_else.sql" ';
    copy cmd_result from program 'psql  -f /fullpath/do_a_final_thing.sql  " ';

 End;
 $$ language plpgsql;

And before calling the function create the temporary table results...

select run_all_procedures(); 
select * from cmd_result;

Please use fullpath (starting with /) instead of mydir, because postgresql will run the commands in the database directory. If u are in doubt about what i mean try:

   create table cmd_result(str text); 
   copy cmd_result from program '/bin/ls';
   select * from cmd_result;

That's all. ** Please select as the correct answer if solves your problem.

Improve this answer
4
  • Thanks Luciano. I'm working on this now. Just curious, is there no way to just run the psql command directly in the procedure? Meaning that if I select run_all_procedures(); the function would simply run psql -f /fullpath/do_something.sql? Commented Sep 28, 2018 at 19:09
  • Well the function will run the command.... You can create a temporary table inside the function code if you dont like to create the table outside. Commented Sep 28, 2018 at 19:10
  • @Lee let me know, is that right ? Can you mark my answer as the correct answer? Commented Sep 3, 2019 at 16:12
  • Great answer @LucianoAndressMartini ! Works fine on Postgres 14. Commented Jul 11, 2022 at 10:27

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.