Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Visit Stack Exchange

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

Sign up to join this community

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams You can use an untrusted language like PL/perl which would allow you to call operating system commands: postgresql.org/docs/current/static/plperl-trusted.html – user1822 Feb 4, 2016 at 7:05

You can easily do what @a_horse_with_no_name suggests in his comment. But there is also an interesting way to do it, using PL/pgSQL as the function language.

This uses a feature of the COPY command, introduced in PostgreSQL 9.3. It can now take a command as target/source, exactly where you'd use a filename or STDIN/STDOUT in normal cases:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
Obviously, you need a table to put the output, but you can ignore it, if you want.  
See a small example:
CREATE TABLE trigger_test (
    tt_id serial PRIMARY KEY,
    command_output text
CREATE OR REPLACE FUNCTION trigger_test_execute_command()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $BODY$
BEGIN
    COPY trigger_test (command_output) FROM PROGRAM 'echo 123';
    RETURN NULL;
$BODY$;
CREATE TABLE trigger_test_source (
    s_id integer PRIMARY KEY
CREATE TRIGGER tr_trigger_test_execute_command
    AFTER INSERT
    ON trigger_test_source
    FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_test_execute_command();
INSERT INTO trigger_test_source VALUES (2);
TABLE trigger_test;
 tt_id │ command_output 
───────┼────────────────
     1 │ 123
Note: the function needs to run with superuser rights - that is, either do the INSERT as a superuser, or define the function with SECURITY DEFINER.  In any other case, you'll get an error: 
ERROR:  must be superuser to COPY to or from an external program
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
                Note that the TO PROGRAM portion requires a string literal, so if you want a dynamic command you have to wrap the whole thing in EXECUTE.  See stackoverflow.com/a/42626659/5419599.
– Wildcard
                Nov 25, 2017 at 5:31
If you just need to look at something relative to $PGDATA you can use pg_ls_data 
SELECT pg_ls_dir('pg_xlog');
Otherwise, a simple function like this:
CREATE OR REPLACE FUNCTION ls(location text) RETURNS text AS $BODY$
    use warnings;
    use strict;
    my $location = $_[0];
    my $output = `ls -l $location`;
    return($output);
$BODY$ LANGUAGE plperlu;
Will give you output like this:
user1@[local]:5432:user1:=# SELECT * FROM ls('/usr/local/pgsql/data');
-----------------------------------------------------------------------------------------
 total 104                                                                              +
 -rw-------  1 pgsql  pgsql      4 Jan 14 14:33 PG_VERSION                              +
 drwx------  8 pgsql  pgsql      8 Jan 15 12:27 base                                    +
 drwx------  2 pgsql  pgsql     54 Feb  4 01:30 global                                  +
 drwx------  2 pgsql  pgsql      4 Jan 15 12:57 pg_clog                                 +
 drwx------  2 pgsql  pgsql      2 Jan 14 14:33 pg_commit_ts                            +
 drwx------  2 pgsql  pgsql      2 Jan 14 14:33 pg_dynshmem                             +
 -rw-------  1 pgsql  pgsql   4458 Feb  4 01:29 pg_hba.conf                             +
 -rw-------  1 pgsql  pgsql   1725 Jan 20 15:29 pg_ident.conf                           +
 drwx------  4 pgsql  pgsql      5 Feb  4 02:14 pg_logical                              +
 drwx------  4 pgsql  pgsql      4 Jan 14 14:33 pg_multixact                            +
 drwx------  2 pgsql  pgsql      3 Feb  4 01:29 pg_notify                               +
 drwx------  2 pgsql  pgsql      2 Jan 14 14:33 pg_replslot                             +
 drwx------  2 pgsql  pgsql      2 Jan 14 14:33 pg_serial                               +
 drwx------  2 pgsql  pgsql      2 Jan 14 14:33 pg_snapshots                            +
 drwx------  2 pgsql  pgsql      2 Feb  4 01:29 pg_stat                                 +
 drwx------  2 pgsql  pgsql      8 Feb  4 02:17 pg_stat_tmp                             +
 drwx------  2 pgsql  pgsql      3 Jan 15 13:08 pg_subtrans                             +
 drwx------  2 pgsql  pgsql      2 Jan 14 14:33 pg_tblspc                               +
 drwx------  2 pgsql  pgsql      2 Jan 14 14:33 pg_twophase                             +
 lrwxr-xr-x  1 pgsql  pgsql     29 Jan 14 14:34 pg_xlog -> /usr/local/pgsql/xlog/pg_xlog+
 -rw-------  1 pgsql  pgsql     88 Jan 14 14:33 postgresql.auto.conf                    +
 -rw-------  1 pgsql  pgsql  21821 Jan 20 15:27 postgresql.conf                         +
 -rw-------  1 pgsql  pgsql     53 Feb  4 01:29 postmaster.opts                         +
 -rw-------  1 pgsql  pgsql     79 Feb  4 01:29 postmaster.pid                          +
(1 row)
Time: 4.361 ms
user1@[local]:5432:user1:=#
                Please note that this can also only be run with superuser rights (or using SECURITY DEFINER).
– dezso
                Feb 4, 2016 at 10:29
Python-equivalent alternative to @Kassandry's Perl solution.
CREATE OR REPLACE FUNCTION ls(location text) RETURNS text AS $BODY$
    global location
    import subprocess
    return subprocess.check_output(["ls", "-l", location])
$BODY$ LANGUAGE plpythonu;
                FYI it's plpython3u in python3 (sudo apt install postgresql-plpython3-12 in Ubuntu 18 and then create extension plpython3u in psql to install the language into postgres)
– DragonMoon
                Apr 14, 2022 at 6:38
        
Thanks for contributing an answer to Database Administrators Stack Exchange!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.