LogoLogo
API Documentation
Version 1.19.5 and Older
Version 1.19.5 and Older
  • DecisionRules Documentation
  • API
    • API Introduction
    • API Keys
      • Solver API Keys
      • Management API keys
      • BI API keys
    • Rule Solver API
    • Management API
      • Deprecated Endpoints
    • Console Logs API
    • Business Intelligence API
      • Deprecated Endpoints
    • Datacenters & Locations
      • Global Cloud
      • Regional Cloud
    • Apache Kafka Solver API
    • Endpoint Settings
    • Archive
      • Rule Flow Solver API (DEPRECATED)
  • Decision tables
    • Decision Tables Introduction
    • Table Designer
    • Input & Output JSON Model
      • Simple Editor
      • JSON Editor
      • Binding to Model
    • Supported Data Types
    • Operators and Functions
      • Basic operators
      • Date operators
      • Functions
        • Logical Functions
        • Math Functions
        • Date and Time Functions
        • Text Functions
        • Data Functions
        • Array Functions
        • Integration functions
        • Functions and JSON
    • Export & Import of Decision Tables
      • Export Decision Table
      • Import Decision Table
      • File Structure of JSON Format
      • Managing Decision Table in Excel/Google Sheets
      • Deprecated Formats: XLSX v.1 and CSV
    • Table Operations
      • Filter Values
      • Valid Values
      • Sorting
  • Decision Trees
    • Decision Trees Introduction
    • Decision Tree Designer
    • Export & Import Decision Trees
      • Export Decision Tree
      • Import Decision Tree
  • Scripting Rules
    • Scripting Rule Introduction
    • Custom functions in Scripting Rules
    • Calling external API within ScriptingRules
    • Use Rule Variables in Scripting Rules
    • Call Embedded Rules in Scripting Rules
    • Export & Import Scripting Rules
      • Export Scripting Rule
      • Import Scripting Rule
    • Tips
  • Rule Flow
    • Rule Flow Designer
    • Rule Flow Mapping
    • Rule States in Rule Flow
    • Warnings & Errors
    • Rule Flow Limits
    • Export & Import Rule Flows
      • Export Rule Flow
      • Import Rule Flow
  • Workflow
    • Workflow Introduction
    • Workflow Designer
    • Workflow Nodes Overview
    • Workflow Limits
  • Other
    • Rule Alias
    • Execution Strategy
    • Rule State
    • Rule Versioning
    • Favorite Rules
    • Rule Variables
    • Rule Comparison
      • Decision Table Comparison
      • Decision Tree Comparison
      • Scripting Rule Comparison
    • Rule Tags
    • Rule Dependencies
    • Test Bench
    • Single Sign-On (SSO)
    • Event timeline
    • Rule Lock
    • Rule Migration Strategies
    • Changes in Version 1.19.0 (10/2024)
  • Organizations
    • Introduction
      • Access to Organization
    • Structure
      • Organization Roles
      • Members
      • Teams
      • Spaces
      • Space Roles
      • Policies
      • Settings
  • Teamwork
    • Dashboard
    • Folders
    • Spaces
    • Manage Spaces
    • Share Rules Between Spaces
    • Users & Roles
    • Teamwork Indicator
  • SDK and Integrations
    • Languages / Frameworks
      • SQL Server
      • Oracle PL/SQL
      • PostgreSQL
      • JavaScript
      • Java Spring Example
      • PHP Library
      • Python Library
      • .NET Library
      • Google Tag Manager
    • Excel Add-in
  • Business Intelligence
    • Audit Logs
    • Create a Power BI Report
    • Connect Power BI to Business Intelligence API
    • Connecting from Power BI (deprecated)
    • Connect DecisionRules to Power BI Using Our Custom Connector
  • Billing
    • Invoices & Billing
    • Change Product Plan
    • Billing Information
    • Plan Limits Explained
  • Regional Cloud
    • Regional Cloud
    • Region Specific API URLs
  • On-Premise / Docker
    • Environment Variables
    • Redis Connection Modes
    • Setup Single Sign-On (SSO)
      • Set up Microsoft Entra ID SSO
      • Set up Google SSO
    • DecisionRules Application
      • Minimal Requirements
      • DecisionRules Server
      • DecisionRules Client
      • DecisionRules Business Intelligence
      • Networking Between Docker Containers
    • Docker Showcase App
      • Showcase
      • Showcase + Business Intelligence
    • AWS Setup
      • AWS ECS/Fargate
      • Cache - Amazon ElastiCache
    • Microsoft Azure Setup
      • Database - Azure CosmosDB
      • Cache - Azure Cache for Redis
      • Azure Container Apps
    • Azure Red Hat OpenShift
    • Google Kubernetes Engine (GKE)
    • Kubernetes Setup
      • Kubernetes Setup with Business Intelligence
    • Logging options
    • CD/CI Pipelines
      • Azure DevOps CICD Pipelines
      • Using Migration script (old way)
    • Offline License
  • Terms & Conditions
    • Terms and Conditions
    • Privacy Policy
    • Service Level Agreement
      • Community Support
      • Standard Cloud (SaaS)
      • Silver SLA
      • Gold SLA
      • Custom SLA
    • Sub-Processor List
  • Roadmap 🚲 🗺️
  • Release Notes
    • Public Cloud
    • On-Premise / Private Cloud
Powered by GitBook
On this page
  • Database Setup
  • Calling API from postgres
  • Processing the API call
  • Load data into JSON
  • Executing the API call
  • Extracting data from the response

Was this helpful?

  1. SDK and Integrations
  2. Languages / Frameworks

PostgreSQL

Was this helpful?

Database Setup

Postgres itself does not have a way of calling an external API, you will need to use a Procedural Language of your choice to create a stored procedure and call the procedure from your code.

In this showcase we will use the plpython3u language ( documentation: ), but you can use any other supported language where you write an analogous procedure.

Bellow you can find scripts that will create the tables necessary for the purposes of this tutorial

Calling API from postgres

As we mentioned we will need to create a stored procedure using a Procedural language and compile it in Postgres. In the case of using plpython3u the procedure is as follows

CREATE OR REPLACE FUNCTION py_pgrest(p_url text, p_method text DEFAULT 'POST'::text, p_data text DEFAULT ''::text, p_headers text DEFAULT '{"Content-Type": "application/json"}'::text)
 RETURNS text
 LANGUAGE plpython3u
AS $function$
    import requests, json
    try:
        r = requests.request(method=p_method, url=p_url, data=p_data, headers=json.loads(p_headers))
    except Exception as e:
        return e
    else:
        return r.content
$function$
;

We can then use this procedure to call the API inside our main block of code.

Processing the API call

Our code divides the input data into batches according to the batchSize variable and for each of these batches call the py_pgrest function to execute the API call. We describe the code in detail bellow, but here is the full version

Load data into JSON

To create a json structure from our input data we use two inbuilt postgres functions

  • JSON_BUILD_OBJECT ( [alias, columnName] , ... )

    • creates a json object with alias as the key and columnName as value

  • JSON_AGG

    • creates an array ( used to create Bulk input )

select json_build_object( 'data', json_agg(json_build_object( 'promoCode' , promoCode , 'productType', productType, 'period', period)) )
		into body
		from (select * from my_dr_input dr OFFSET lastBatchStart - 1 FETCH FIRST batchSize rows only);

Executing the API call

To execute the API call we simply invoke the previously created py_pgrest function using the following code

 py_pgrest(  'https://api.decisionrules.io/rule/solve/' || rule_alias,
					   'POST' , 
					   body, 
					   '{"Authorization":"Bearer ' || API_KEY ||  '","Content-Type": "application/json"}');

Extracting data from the response

In our example we extract data from the API response and store it in the RESULTS table. To access the data in the response we use the following process

result =  py_pgrest(  'https://api.decisionrules.io/rule/solve/' || rule_alias,
					   'POST' , 
					   body, 
					   '{"Authorization":"Bearer ' || API_KEY ||  '","Content-Type": "application/json"}');
-- store results in a table
-- use -> to access nested object
-- use ->> to extract the value
-- use ::data_type to convert the value into your desired datatype
insert into results
select (res::json->'prices'->>'finalPrice')::float ,
       (res::json->'prices'->>'crudePrice')::float ,
        res::json->>'message' 
-- parse the response by object
from (select JSON_ARRAY_ELEMENTS(JSON_ARRAY_ELEMENTS(result::json) ) as res ) ;

We use the function JSON_ARRAY_ELEMENTS twice since the strucure of the response is

[ [result1...resultN], [result1..resultM] , ...   ]

This gives us a row for each result we obtained. We then take these rows ( column alias res ) and we extract the necessary values using two different operators

  • '- >' allows us to gain access into nested records

  • '- > >' extracts the final value

we then can use ::your_data_type to convert the value to the required data type

https://www.postgresql.org/docs/current/plpython.html
443B
create My_DR_Input.sql
Create My_DR_input table
341B
create results.sql
Create RESULTS table
469B
RestCallFunction.sql
plpython3u function code
1KB
postgres Call and process api.sql
Full code to process input in batches and store results