Call REST Endpoints from PostgreSQL Trigger

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


#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:


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(''+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 org.apache.log4j.Level
import org.apache.log4j.Logger

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

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

And we get in Jira’s log file:

2020-12-12 22:21:50,963+0100 http-nio- INFO admin 1341x295x2 fjakwz /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


Reach out to me for professional support!