Execute External Program with Trigger in Postgres 9.4

execute external program with trigger in postgres 9.4

Disclamer: I work with Andreas Fritsch on the same project.

We have solved this problem in the following way.

There is an "Language"-extension PL/sh Procedural Language Handler for PostgreSQL coded by Peter Eisentraut which does exactly what we need.

You define a shell-script like this:

CREATE or REPLACE FUNCTION test(text) RETURNS text AS '
#!/bin/bash
echo Test: $1 is working
' LANGUAGE plsh;

This is an example of a trigger-function with some usefull environment-variables for triggers:

CREATE or REPLACE FUNCTION TriggerTest() RETURNS trigger AS $$
#!/bin/bash
#mkdir /has/triggertest/$PLSH_TG_NAME
cd /has/triggertest
touch PLSH_TG_NAME-$PLSH_TG_NAME
touch PLSH_TG_WHEN-$PLSH_TG_WHEN
touch PLSH_TG_LEVEL-$PLSH_TG_LEVEL
touch PLSH_TG_OP-$PLSH_TG_OP
touch PLSH_TG_TABLE_NAME-$PLSH_TG_TABLE_NAME
touch PLSH_TG_TABLE_SCHEMA-$PLSH_TG_TABLE_SCHEMA
touch new-$new.x
#touch "arg-0-'$0'"
touch "arg-1-'$1'"
touch "arg-2-'$2'"
touch "arg-3-'$3'"
touch "arg-4-'$4'"

for arg do
    touch "Arg is '$arg'"
done

exit 0
$$ LANGUAGE plsh;

You create a before-insert-trigger with the following SQL-Statement

CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest2
    FOR EACH ROW EXECUTE PROCEDURE TriggerTest(new);

I hope this helps anybody else who is looking for a similar solution for his problem.

Call command-line function in Perl and get output as String

You can use backticks. Quoting from perldoc perlop:

*qx/STRING/

*`STRING`

A string which is (possibly) interpolated and then
executed as a system command with /bin/sh or its equivalent. Shell
wildcards, pipes, and redirections will be honored. The collected
standard output of the command is returned; standard error is
unaffected. In scalar context, it comes back as a single (potentially
multi-line) string, or undef if the command failed. In list context,
returns a list of lines (however you've defined lines with $/ or
$INPUT_RECORD_SEPARATOR), or an empty list if the command failed.

You can't use system for this, since it just returns the return value of the argument (when run as a shell command). This should work:

CREATE OR REPLACE FUNCTION perl_func()
  RETURNS character varying AS
$BODY$
  my $output=`java -version`;
  chomp($output);
  return $output;
$BODY$
  LANGUAGE plperlu VOLATILE
  COST 100;

Note that the output of a backticked command usually includes a trailing newline, so it's often useful to get rid of that via chomp.

Passing user id to PostgreSQL triggers

Options include:

When you open a connection, CREATE TEMPORARY TABLE current_app_user(username text); INSERT INTO current_app_user(username) VALUES ('the_user');. Then in your trigger, SELECT username FROM current_app_user to get the current username, possibly as a subquery.
In postgresql.conf create an entry for a custom GUC like my_app.username = 'unknown';. Whenever you create a connection run SET my_app.username = 'the_user';. Then in triggers, use the current_setting('my_app.username') function to obtain the value. Effectively, you're abusing the GUC machinery to provide session variables. Read the documentation appropriate to your server version, as custom GUCs changed in 9.2.
Adjust your application so that it has database roles for every application user. SET ROLE to that user before doing work. This not only lets you use the built-in current_user variable-like function to SELECT current_user;, it also allows you to enforce security in the database. See this question. You could log in directly as the user instead of using SET ROLE, but that tends to make connection pooling hard.

In both all three cases you're connection pooling you must be careful to DISCARD ALL; when you return a connection to the pool. (Though it is not documented as doing so, DISCARD ALL does a RESET ROLE).

Common setup for demos:

CREATE TABLE tg_demo(blah text);
INSERT INTO tg_demo(blah) VALUES ('spam'),('eggs');

-- Placeholder; will be replaced by demo functions
CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
SELECT 'unknown';
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION tg_demo_trigger() RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'Current user is: %',get_app_user();
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_demo_tg
AFTER INSERT OR UPDATE OR DELETE ON tg_demo 
FOR EACH ROW EXECUTE PROCEDURE tg_demo_trigger();

Using a GUC:

In the CUSTOMIZED OPTIONS section of postgresql.conf, add a line like myapp.username = 'unknown_user'. On PostgreSQL versions older than 9.2 you also have to set custom_variable_classes = 'myapp'.
Restart PostgreSQL. You will now be able to SHOW myapp.username and get the value unknown_user.

Now you can use SET myapp.username = 'the_user'; when you establish a connection, or alternately SET LOCAL myapp.username = 'the_user'; after BEGINning a transaction if you want it to be transaction-local, which is convenient for pooled connections.

The get_app_user function definition:

CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
    SELECT current_setting('myapp.username');
$$ LANGUAGE sql;

Demo using SET LOCAL for transaction-local current username:

regress=> BEGIN;
BEGIN
regress=> SET LOCAL myapp.username = 'test_user';
SET
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: test_user
INSERT 0 1
regress=> COMMIT;
COMMIT
regress=> SHOW myapp.username;
 myapp.username 
----------------
 unknown_user
(1 row)

If you use SET instead of SET LOCAL the setting won't get reverted at commit/rollback time, so it's persistent across the session. It is still reset by DISCARD ALL:

regress=> SET myapp.username = 'test';
SET
regress=> SHOW myapp.username;
 myapp.username 
----------------
 test
(1 row)

regress=> DISCARD ALL;
DISCARD ALL
regress=> SHOW myapp.username;
 myapp.username 
----------------
 unknown_user
(1 row)

Also, note that you can't use SET or SET LOCAL with server-side bind parameters. If you want to use bind parameters ("prepared statements"), consider using the function form set_config(...). See system adminstration functions

Using a temporary table

This approach requires the use of a trigger (or helper function called by a trigger, preferably) that tries to read a value from a temporary table every session should have. If the temporary table cannot be found, a default value is supplied. This is likely to be somewhat slow. Test carefully.

The get_app_user() definition:

CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
DECLARE
    cur_user text;
BEGIN
    BEGIN
        cur_user := (SELECT username FROM current_app_user);
    EXCEPTION WHEN undefined_table THEN
        cur_user := 'unknown_user';
    END;
    RETURN cur_user;
END;
$$ LANGUAGE plpgsql VOLATILE;

Demo:

regress=> CREATE TEMPORARY TABLE current_app_user(username text);
CREATE TABLE
regress=> INSERT INTO current_app_user(username) VALUES ('testuser');
INSERT 0 1
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: testuser
INSERT 0 1
regress=> DISCARD ALL;
DISCARD ALL
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: unknown_user
INSERT 0 1

Secure session variables

There's also a proposal to add "secure session variables" to PostgreSQL. These are a bit like package variables. As of PostgreSQL 12 the feature has not been included, but keep an eye out and speak up on the hackers list if this is something you need.

Advanced: your own extension with shared memory area

For advanced uses you can even have your own C extension register a shared memory area and communicate between backends using C function calls that read/write values in a DSA segment. See the PostgreSQL programming examples for details. You'll need C knowledge, time, and patience.

How to track changes in any function in PostgreSQL

In Postgres 9.5 there is a function pg_event_trigger_ddl_commands() which can be used in an event trigger to get an oid of inserted/altered object.

Log table:

create table function_log (
    datetime timestamp, 
    schema_name text, 
    function_name text, 
    tag text, 
    function_body text);

Event function and trigger:

create or replace function public.on_function_event()
    returns event_trigger
    language plpgsql
as $function$
begin
    insert into function_log
    select now(), nspname, proname, command_tag, prosrc
    from pg_event_trigger_ddl_commands() e
    join pg_proc p on p.oid = e.objid
    join pg_namespace n on n.oid = pronamespace;
end
$function$;

create event trigger on_function_event
on ddl_command_end 
when tag in ('CREATE FUNCTION', 'ALTER FUNCTION')
execute procedure on_function_event();

Example:

create or replace function test()
returns int as $$ select 1; $$ language sql;

create or replace function test()
returns int as $$ select 2; $$ language sql;

alter function test() immutable;

select *
from function_log;

          datetime          | schema_name | function_name |       tag       | function_body 
----------------------------+-------------+---------------+-----------------+---------------
 2017-02-26 13:05:15.353879 | public      | test          | CREATE FUNCTION |  select 1; 
 2017-02-26 13:05:15.353879 | public      | test          | CREATE FUNCTION |  select 2; 
 2017-02-26 13:05:15.353879 | public      | test          | ALTER FUNCTION  |  select 2; 
(3 rows)

You can add DROP FUNCTION command tag to the trigger and then use the function pg_event_trigger_dropped_objects() in analogous way to pg_event_trigger_ddl_commands().

Unfortunately, there is no pg_event_trigger_ddl_commands() in Postgres 9.4. You could try to get an inserted/altered object using current_query() or write a trigger function in C. I think the easier way would be to upgrade Postgres to 9.5+.

WildFly 10 with PostgreSQL 9.4 datasource Error deploying application

I have been working with Wildfly10 with posgresql-9.3, defining datasources directly in standalone-full.xml file have worked for me. So I hope this help you.

I have created directories under modules for the PostgreSQL files. The structure is modules/org/postgres/main

Do this in your standalone-full.xml which is under standalone>configuration

in that file inside <datasources> add this

 <datasource jndi-name="java:jboss/datasources/PAYROLL_DS" pool-name="PAYROLL_DS" enabled="true" use-java-context="true">
                <connection-url>jdbc:postgresql://localhost:5432/payroll_db</connection-url>
                <driver>postgresql</driver>
                <security>
                    <user-name>test</user-name>
                    <password>test@123</password>
                </security>
  </datasource>

this is my persistence.xml file

 <?xml version="1.0" encoding="UTF-8"?>
            <persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
              <persistence-unit name="PAYROLL_PU" transaction-type="JTA">
                <jta-data-source>java:/jboss/datasources/PAYROLL_DS</jta-data-source>

                <properties>
                  <property name="hibernate.hbm2ddl.auto" value="update"/>
                  <property name="hibernate.show_sql" value="true"/>
                  <property name="hibernate.format_sql" value="false"/>
                  <property name="hibernate.case.use_query_cache" value="false"/>
                  <!-- 2nd level cache -->
                </properties>
              </persistence-unit>
            </persistence>

When you add datasources through standalone-full.xml you dont need to create module.xml to present postgres. You just simply edit standalone-full.xml inside <drivers> add this

<driver name="postgresql" module="org.postgresql">
                    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
</driver>

Execute External Program with Trigger in Postgres 9.4