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.

0

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.

  • 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? – Lee Sep 28 '18 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. – Luciano Andress Martini Sep 28 '18 at 19:10
  • @Lee let me know, is that right ? Can you mark my answer as the correct answer? – Luciano Andress Martini Sep 3 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.