Call REST Endpoints from PostgreSQL Trigger

This example for: member added to a Jira Tempo team. Figure out the updated tables.

/etc/postgresql/<version>/<cluster>/postgresql.conf

#log_statement = 'none' # none, ddl, mod, all
log_statement = 'all' # none, ddl, mod, all

Don’t forget setting back this statement after you have seen on which tables you need to set a trigger.

In PostgreSQL’s log file:

/var/log/postgresql-<version>-<cluster>.log

UPDATE public."AO_AEFED0_TEAM_MEMBER_V2" SET "MEMBER_KEY" = $1, "MEMBER_TYPE" = $2, "TEAM_ID" = $3 WHERE "ID" = $4
2020-12-12 19:59:50.465 CET [33809] atlassian@jira_temp_ar DETAIL: parameters: $1 = 'JIRAUSER10000', $2 = 'USER', $3 = '20', $4 = '3'

PostgreSQL Trigger

sudo apt install postgresql-plpython3-10

Now open your database with psql, don’t forget to change the database:

\c <db name>

Create an extension:

create extension plpython3u;

The procedure we will call with our trigger:

CREATE or replace FUNCTION tempoMemberAddedToTeam () RETURNS TRIGGER
AS $$
import requests
from requests.auth import HTTPBasicAuth
requests.put('http://127.0.0.1:8180/jira/rest/my-groovy/latest/custom/add_tempo_team_member_to_advanced_roadmaps_shared_team?userkey='+str(TD["new"]["MEMBER_KEY"])+'&teamid='+str(TD["new"]["TEAM_ID"]), auth=('admin', 'admin'))
$$ LANGUAGE plpython3u;

And the trigger:

create trigger tempoMemberAddedToTeam after update on public."AO_AEFED0_TEAM_MEMBER_V2" for each row execute procedure tempomemberaddedtoteam();

Jira REST Endpoint

We do this with the great Jira add-on myGroovy:

import javax.ws.rs.core.MultivaluedMap
import org.apache.log4j.Level
import org.apache.log4j.Logger

def myLog = Logger.getLogger("Add Tempo team member to AR shared team")
myLog.setLevel(Level.ALL)

MultivaluedMap<String, String> queryParams = uriInfo.getQueryParameters(true)
def userKey = queryParams.getFirst("userkey")
def tempoTeamId = queryParams.getFirst("teamid")
myLog.info("userKey: " + userKey + ", tempoTeamId: " + tempoTeamId)

And we get in Jira’s log file:

2020-12-12 22:21:50,963+0100 http-nio-127.0.0.1-8180-exec-18 INFO admin 1341x295x2 fjakwz 127.0.0.1 /rest/my-groovy/latest/custom/add_tempo_team_member_to_advanced_roadmaps_shared_team [Add Tempo team member to AR shared team] userKey: JIRAUSER10000, tempoTeamId: 20

Done.

Reach out to me for professional support!

Contact