Issue Passing Json Object/Array to Postgres Func, Syntax?

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$;

Hi @Dhof!

I think you’ll have to stringify the object before passing it into the query, or else it will just be passing in a JS object ( [Object] ):

So your transformer would be:

and then add with the quotes:

Hopefully this helps!

1 Like