# Running a psql command inside a Postgres function

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.

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;


• 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