Running a psql command inside a Postgres function

Question

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.

Luciano Andress Martini · Accepted Answer · 2018-09-28 18:53:19Z

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.

edited Sep 28, 2018 at 18:53

answered Sep 28, 2018 at 18:29

Luciano Andress Martini

1,6113 gold badges16 silver badges30 bronze badges

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
– Lee

2018-09-28 19:09:13 +00:00
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.

Luciano Andress Martini
– Luciano Andress Martini

2018-09-28 19:10:33 +00:00
Commented Sep 28, 2018 at 19:10
@Lee let me know, is that right ? Can you mark my answer as the correct answer?

Luciano Andress Martini
– Luciano Andress Martini

2019-09-03 16:12:45 +00:00
Commented Sep 3, 2019 at 16:12
Great answer @LucianoAndressMartini ! Works fine on Postgres 14.

Binita Bharati
– Binita Bharati

2022-07-11 10:27:02 +00:00
Commented Jul 11, 2022 at 10:27

Add a comment |

Stack Exchange Network

Running a psql command inside a Postgres function

1 Answer 1

Your Answer

Hot Network Questions

Running a psql command inside a Postgres function

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions