6

My requirement is to execute a system command like (ls) or C program when a trigger executes. Is there any way to create a trigger function to solve this problem.

11

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;
END;
$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.
5

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');
                                       ls                                            
-----------------------------------------------------------------------------------------
 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:=#
  • 1
    Please note that this can also only be run with superuser rights (or using SECURITY DEFINER). – dezso Feb 4 '16 at 10:29
  • @dezso Quite right. Thank you for adding that point. =) – Kassandry Feb 4 '16 at 22:02

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.