Passing array with varying dimensions to postgres function [closed]

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Guide the asker to update the question so it focuses on a single, specific problem. Narrowing the question will help others answer the question concisely. You may edit the question if you feel you can improve it yourself. If edited, the question will be reviewed and might be reopened.

Closed 4 years ago.

Improve this question

I am trying to process a group of transactions as one transaction with Postgres.

I receive a group of transactions to process which I want to pass to Postgres to process all together or not at all.

There are a few different types of transactions that each have their own stored procedure to handle. Ideally I am looking to pass a Javascript array of dictionaries for the Postgres function to iterate through and then check a field for the type of job to call the correct procedure. Sort of like this pseduo code

create or replace procedure processblock(  array )
begin
   for item in array
       if item.jobtype = 'buyitem'
            call buyitem(item.name, item.quantity)
       if item.jobtype = 'sellitem'
            call sellitem(item.name, item.quantity)
       if item.jobtype = 'postad'
            call postad(item.adname, item.adtext, item.adurl)
       if item.jobtype = 'updatestate'
            call updatestate(item.laststate)

So I want to be able pass a group of jobs that get handled by a function by calling the appropriate stored procedure with have varying arguments (array of dictionaries or json) meaning I need to pass items of various sizes.

I am using Postgres 13. I have seen this question, and the answer, but it doesn't seem to cover an array of variadic arguments.

AdamKG · Accepted Answer · 2021-08-20 22:09:11Z

Here's an example of how to do it. The important part is to keep it as the json type until you get to the dispatching part; at that point, just extract the relevant args for that sub-type using regular json operators (see dispatching_function in the example).


-- in dispatching_func.psql
CREATE OR REPLACE FUNCTION function_the_first(thing_name text, quantity integer, out bar text)
as $$
BEGIN
RAISE NOTICE 'first_kind, thing %, quantity %', thing_name, quantity;
return;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION function_the_second(adname text, adtext text, adurl text, out foo text)
as $$
BEGIN
RAISE NOTICE 'second_kind, adname %, adtext %, adurl %', adname, adtext, adurl;
return;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dispatching_function(input_data json, out foo text)
AS $$
BEGIN
IF ((input_data->>'kind') = 'first_kind') THEN
  perform function_the_first(input_data->>'name', (input_data->>'quantity')::integer);
ELSIF ((input_data->>'kind') = 'second_kind') THEN
  perform function_the_second(input_data->>'adname', input_data->>'adtext', input_data->>'adurl');
END IF;
return;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dispatching_function_per_element(input_data json, out foo text)
AS $$
DECLARE elm json;
BEGIN
FOR elm in SELECT elem from json_array_elements(input_data) as elem
LOOP
PERFORM dispatching_function(elm::json);
END LOOP;
END
$$ LANGUAGE plpgsql;

----

testdb=# \i dispatching_func.psql 
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
testdb=# select dispatching_function_per_element(
$$
[{"kind": "first_kind", "name": "asdf", "quantity": 2},
 {"kind": "second_kind", "adname": "my ad", "adtext": "buy stuff", "adurl": "example.com"}]
 $$::json);
NOTICE:  first_kind, thing asdf, quantity 2
NOTICE:  second_kind, adname my ad, adtext buy stuff, adurl example.com
 dispatching_function_per_element 
----------------------------------
 
(1 row)

Stack Exchange Network

Passing array with varying dimensions to postgres function [closed]

1 Answer 1

Linked

Hot Network Questions

Passing array with varying dimensions to postgres function [closed]

1 Answer 1

Linked

Related

Hot Network Questions