LogoLogo
API Documentation
Current Version
Current Version
  • DecisionRules Documentation
  • DecisionRules Academy
  • API
    • API Introduction
    • API Keys
      • Solver API Keys
      • Management API keys
      • BI API keys
    • Rule Solver API
    • Management API
      • Rule Migration Strategies
      • 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)
  • AI Assistant
    • About Assistant
  • RULES
    • Rules Introduction
    • Rule List
    • Rule Mode
    • Common Rule Features
      • Input & Output Model
        • Simple Editor
        • JSON Editor
      • Test Bench
      • Rule Alias
      • Rule State
      • Versioning
      • Rule Variables
      • Execution Strategy
      • Rule Dependencies
      • Rule Export & Import
        • Rule Export
        • Rule Import
        • Managing Decision Table in Excel/Google Sheets
        • Deprecated Formats: XLSX v.1 and CSV
      • Tags
      • Rule Comparison
        • Decision Table Comparison
        • Decision Tree Comparison
        • Scripting Rule Comparison
      • Rule Lock
      • Teamwork Indicator
      • Event Timeline
    • Data Types & Functions
      • 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
    • Decision Table
      • Table Designer
        • Table Operations
          • Filter Values
          • Valid Values
          • Sorting
      • Binding to Model
    • Decision Tree
      • Tree Designer
    • Workflow
      • Workflow Designer
      • Workflow Nodes Overview
      • Workflow Limits
    • Scripting Rule
      • Custom functions in Scripting Rules
      • Calling external API within ScriptingRules
      • Use Rule Variables in Scripting Rules
      • Call Embedded Rules in Scripting Rules
      • Tips
    • Rule Flow
      • Rule Flow Designer
      • Rule Flow Mapping
      • Rule States in Rule Flow
      • Warnings & Errors
      • Rule Flow Limits
  • SPACE
    • Space Introduction
    • Space Info
    • Dashboard
    • Access
    • API Keys
    • Audit Logs
  • Organization
    • Organization Introduction
    • Organization List
    • Members
    • Teams
    • Spaces
    • Space Roles
    • Policies
    • Statistics
    • Settings
  • Profile
    • Profile Introduction
    • General
    • Dashboard
    • Plans
    • Add-ons
    • Limits
      • Plan Limits Explained
    • Subscriptions
    • Invoices
  • Access
    • Sign Up & Login
    • Invitations & Permissions
    • Single Sign-On (SSO)
  • Business Intelligence
    • Audit Logs
    • Power BI Connectivity
      • 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
  • OTHER DEPLOYMENT OPTIONS
    • Regional Cloud
      • Region Specific API URLs
    • Docker & On-Premise
      • Environment Variables
      • Redis Connection Modes
      • DecisionRules Application
        • Minimal Requirements
        • DecisionRules Server
        • DecisionRules Client
        • DecisionRules Business Intelligence
        • Networking Between Docker Containers
      • Setup Single Sign-On (SSO)
        • Set up Microsoft Entra ID SSO
        • Set up Google SSO
      • 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
  • 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
  • Terms & Conditions
    • Terms and Conditions
    • Privacy Policy
    • Service Level Agreement
      • Community Support
      • Standard Cloud (SaaS)
      • Silver SLA
      • Gold SLA
      • Custom SLA
    • Sub-Processor List
  • Product Updates
    • Release Notes
      • Public Cloud
      • On-Premise / Private Cloud
    • Major Updates
      • Changes in Version 1.19.0 (10/2024)
      • Changes in Version 1.20.0 (4/2025)
    • Roadmap
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

Last updated 1 year ago

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