Hologres:Remote UDFs

Create an extension

Run the following command once per database before using remote functions:

CREATE EXTENSION [ IF NOT EXISTS ] function_compute;

Create a function

CREATE OR REPLACE FUNCTION rf_add (
  a INTEGER,
  b INTEGER DEFAULT 1
)
RETURNS BIGINT
LANGUAGE function_compute
AS 'xxxxxxxxxxxxx.cn-shanghai-internal.fc.aliyuncs.com/add'
;

-- Form 1: RETURNS TABLE
CREATE OR REPLACE FUNCTION rf_unnest(TEXT [])
RETURNS TABLE (item TEXT)
LANGUAGE function_compute
AS 'xxxxxxxxxxxxxx.cn-hangzhou-internal.fc.aliyuncs.com/unnest'
STRICT
SET function_compute.max_batch_size TO 1024;

-- Form 2: RETURNS SETOF
CREATE OR REPLACE FUNCTION rf_unnest(TEXT [])
RETURNS SETOF TEXT
LANGUAGE function_compute
AS 'xxxxxxxxxxxxxx.cn-hangzhou-internal.fc.aliyuncs.com/unnest'
STRICT
SET function_compute.max_batch_size TO 1024;

View functions

List all remote functions in the current database:

SELECT
  CASE
    WHEN p.proretset = 'f' THEN 'scalar UDF'
    WHEN p.proretset = 't' THEN 'UDTF'
  END AS function_type,
  n.nspname AS schema_name,
  p.proname AS function_name,
  pg_get_function_arguments(p.oid) AS arguments,
  pg_get_function_result(p.oid) AS return_type,
  p.proisstrict AS is_strict,
  p.proconfig AS config,
  pg_get_functiondef(p.oid) AS definition
FROM
  pg_proc p
  JOIN pg_language l ON p.prolang = l.oid
  JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE
  l.lanname = 'function_compute'
  AND p.prokind != 'p'
ORDER BY
  function_type,
  schema_name,
  function_name;

Delete a function

DROP FUNCTION rf_add(INTEGER, INTEGER);

Request: Hologres to FC

Hologres calls the FC InvokeFunction API with an HTTP POST request. The request body contains a data key holding a two-dimensional array. Each element in the array represents one row of input data for the function call.

Serialization rules:

Request example:

{
  "data": [
    ["foo", 100, true],
    [null, null, false],
    ["bar", 200, false]
  ]
}

FC handler skeleton (Python):

Parse the data field and return a results array. The following skeleton covers the complete request-to-response cycle for a scalar UDF:

import json

def handler(event, context):
    evt = json.loads(event)
    data = evt.get('data', [])

    results = []
    for row in data:
        # row is a list of argument values, e.g. ["foo", 100, True]
        output = process(row)          # replace with your logic
        results.append([output])       # each result must be wrapped in a list

    return json.dumps({'results': results})

For a UDTF that produces multiple output rows per input row, see the response format section below.

Response: FC to Hologres

FC returns results as a JSON object with a results field.

{
  "results": [
    ["Beijing"],
    ["Shanghai"],
    ["Shenzhen"],
    ["Guangzhou"]
  ]
}

{
  "results": [
    [0, "Beijing"],
    [1, "Shanghai"],
    [3, "Shenzhen"],
    [3, "Guangzhou"]
  ]
}

Step 1: Create an FC event-triggered function

1. Log on to the Function Compute console. In the left navigation pane, click Functions and switch to the region where your Hologres instance resides.

2. On the Functions page, click Create Function.

3. Select Event Function and configure the following parameters. Keep the defaults for all other settings. For details, see Create an event-triggered function.

   Parameter	Value
   Function Name	unnest
   Runtime	Built-in Runtimes > Python > Python 3.10
   Code Upload Method	Upload Code via ZIP Package
   Code Package	Save the following code as unnest.py, compress it into unnest.zip, and upload the file.
   Handler	unnest.unnest

   import json
   
   def unnest(event, context):
       evt = json.loads(event)
       data = evt.get('data', None)
       if data is None:
           raise ValueError('no "data" key in event.')
       if not isinstance(data, list):
           raise ValueError('data is not a list.')
   
       res = list()
       for i in range(len(data)):
           if len(data[i]) != 1 or not isinstance(data[i], list):
               raise ValueError('the item in data is not a list.')
           for item in data[i][0]:
               res.append([i, item])
   
       return json.dumps({'results': res})

Step 2: Create the Hologres remote function

Run these statements in HoloWeb or any PostgreSQL-compatible client connected to Hologres. For details, see Connect to HoloWeb.

CREATE EXTENSION IF NOT EXISTS function_compute;

CREATE OR REPLACE FUNCTION rf_unnest(INTEGER [])
  RETURNS SETOF INTEGER
STRICT
LANGUAGE function_compute
AS 'xxxxxxxxxxxxxxxxx.cn-shanghai-internal.fc.aliyuncs.com/unnest';

Step 3: Prepare test data

CREATE TABLE test_array (
    numbers INTEGER[]
);

INSERT INTO test_array (numbers) VALUES
  (ARRAY[1, 3]),
  (ARRAY[2, 4]),
  ('{}'),
  (ARRAY[]::INTEGER[]),
  (NULL);

Step 4: Call the remote function

SELECT numbers, rf_unnest(numbers) FROM test_array;

Expected output:

 numbers | rf_unnest
---------+-----------
 {2,4}   |         2
 {2,4}   |         4
 {1,3}   |         1
 {1,3}   |         3
(4 rows)

Rows with empty arrays or NULL produce no output, because the function is defined with STRICT.