Execute system commands in postgresql

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.

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.

StackExchange Database Administrators Q#128229, answer score: 20