Hi, been trying to pass a json object and/or array to a function in postgres for looping over an object/array to insert the id's from a retool table that has multiselect enabled into a postgres table that's used as dimension table with fk constraints. Spent a considerable amount of time over the weekend trying different approaches and it looks like a syntax issue that's been causing me headaches. What postgres expects and I think I'm sending is not what's being passed.
The current function expects the data in the following format with the outermost single quotes, ', to be included.
SELECT my_function('{"a":1, "b":2, "c":3}');
However whenever i add them to the function it modifies the values.
Data as expected from example code
Data that I want to pass to the function (correct format)
When I pass it in without quotes
Data returned
{ "value": [ { "id": 5, "item": "34234232dfas", "itname": "California Category I Residual Pesticides", "status": "dasfd", "exp_date": "2021-03-05", "batch_lot": "tewst" }, { "id": 4, "item": "34234232", "itname": "California Category I Residual Pesticides", "status": "34121243422", "exp_date": "2021-03-05", "batch_lot": "eq3t432" }, { "id": 3, "item": "342342", "itname": "California Category I Residual Pesticides", "status": "34121422", "exp_date": "2021-03-05", "batch_lot": "eqt432" } ], "funcBody": "// Tip: assign your external references to variables instead of chaining off the curly brackets. \n\nvar omgi = [object Object],[object Object],[object Object]\n\nreturn omgi;", "namespace": null, "pluginType": "Function", "renderedFunction": " // Tip: assign your external references to variables instead of chaining off the curly brackets. \n\n var omgi = ___var0\n\n return omgi;\n" }
Error
If I try to create in the transformer
Working with values typed in
postgres function I'm using as an example
CREATE OR REPLACE FUNCTION test_function(input jsonb)
RETURNS jsonb
LANGUAGE plpgsql AS -- language declaration required
$func$
DECLARE
_key text;
_value text;
BEGIN
FOR _key, _value IN
SELECT * FROM jsonb_each_text($1)
LOOP
-- do some math operation on its corresponding value
RAISE NOTICE '%: %', _key, _value;
END LOOP;
RETURN input;
END
$func$;
