cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X

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

MG_2602
9-Granite

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
nmilleson
17-Peridot
(To:MG_2602)

@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

View solution in original post

3 REPLIES 3
nmilleson
17-Peridot
(To:MG_2602)

@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

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
17-Peridot
(To:MG_2602)

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.

 

Top Tags