Skip to main content
M
MG_26021-Visitor
1-Visitor
September 9, 2022
Solved

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

  • September 9, 2022
  • 2 replies
  • 1909 views

Hi,

 

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

 

@nmilleson @VladimirRosu_116627 , Can you please help me.

Thanks,

Meghna

Best answer by nmilleson

@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

2 replies

N
nmilleson17-PeridotAnswer
17-Peridot
September 9, 2022

@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
    Rocko19-Tanzanite
    19-Tanzanite
    September 12, 2022

    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.

      J
      jensc17-Peridot
      17-Peridot
      October 13, 2022

      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.

       

        Terms & ConditionsAccessibility statement