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.
-
2You 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 – a_horse_with_no_name Feb 4 '16 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;
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.
-
Note that the
TO PROGRAM
portion requires a string literal, so if you want a dynamic command you have to wrap the whole thing inEXECUTE
. See stackoverflow.com/a/42626659/5419599. – Wildcard Nov 25 '17 at 5:31 -
Can you please help me with my question: stackoverflow.com/questions/61071706/… – ankit Apr 7 '20 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');
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:=#
-
1Please note that this can also only be run with superuser rights (or using
SECURITY DEFINER
). – dezso Feb 4 '16 at 10:29 -