How to pass infotable to stored procedure for inserting data in PostgreSQL

[MG_2602]

Hi,

How can we pass intotable as a parameter to a stored procedure for inserting data in postgreSQL table.

@nmilleson @VladimirRosu , Can you please help me.

Thanks,

Meghna

[nmilleson · Accepted solution]

@MG_2602 ,

Unfortunately we cannot pass infotable parameters to the Database Thing SQL services (see here).  You could iterate the infotable into a string that you could pass into a parameter.  Just make sure that the parameter in your service is surrounded by <<>> instead of [[ ]]. Something like:

CALL myProcedure(<<paramString>>);

-Nick

[Rocko]

You can use the toJSON() function on Infotables to serialize them into a string. This is a bit more verbose than if you stringify the infotable yourself, but you have less work recreating the infotable after loading it.

With postgres, you can also use JSON queries to filter on the JSON fields directly in the query, reducing the amount of returned rows.

[jensc]

I am not sure if it would be the same on postgreSQL, but on MSSQL you can do:

If you want to avoid using <<>> parameters you could use a JSON string from the infotable as input. Something like this:

let jsonString = InfoTable.toJSON();
let stringArray = jsonString.rows;

This will give you a string containing an array with all of the rows from the infotable.

You can then pass this into your stored procedure like this:

Here, StringArray is an input parameter with String data type.

exec [StoredProcedure] @json = [[StringArray]]

and some info regarding the stored procedure:

input param is: 

@jsonNVARCHAR(max)

then you can use the OPENJSON function in MSSQL.