To update multiple rows using stored procedure in Postgres

Question

I am trying to figure out how to update a postgres table using an array of object. I want each object in the array to correspond to a new row, with each key corresponding to a column, and each value is the data to be inserted into the column.

Also would like to know how to call that procedure in c#?

Here the the data format:

[
    { col1: a, col2: 5, col3: 1, col4: one},
    { col1: b, col2: 6, col3: 2, col4: two},
    { col1: c, col2: 7, col3: 3, col4: three},
    { col1: d, col2: 8, col3: 4, col4: four},
]

Here is my expected output:

 col1   (varchar)| col2 (integer) |   col3 (integer)   |   col4 (varchar)
-----------------+----------------+--------------------+------------------
    a            |  5             |     1              |    one
    b            |  6             |     2              |    two
    c            |  7             |     3              |    three
    d            |  8             |     4              |    four   

 I am passing the data format as array in stored procedure.
    But want to know, how to cal the SP from c#?


The stored procedure I have written is:  

CREATE OR REPLACE FUNCTION dbo.sp_insertorupdatereadings(d dbo.reading[])
  RETURNS boolean AS
$BODY$
DECLARE

begin
--Update min values
update dbo.reading set 
  p7_1_5_1_0_first =subquery.p7_1_5_1_0_first,
  p7_1_5_1_0_last =subquery.p7_1_5_1_0_last,
  p7_1_5_2_0_first=subquery.p7_1_5_2_0_first,
  p7_1_5_2_0_last=subquery.p7_1_5_2_0_last
  From (select * from unnest(d)) as subquery
  where dbo.reading.p7_1_5_1_0_first= subquery.p7_1_5_1_0_first;

-- insert new records
  insert into dbo.reading 
select * from unnest(d) as inserd where (id) not in (select id from dbo.reading);
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION dbo.reading(dbo.reading[])
  OWNER TO postgres;

using npgsql for postgre npgsql.org/doc/index.html

Hassaan
– Hassaan

2018-11-16 13:33:33 +00:00
Commented Nov 16, 2018 at 13:33 — Hassaan
– Hassaan, Commented Nov 16, 2018 at 13:33

Hambone · Accepted Answer · 2018-11-17 19:24:36Z

1

It would be a huge help to actually see the stored procedure and any relevant .NET objects, so most of the advice I can offer is only notional.

Unlike other ADO adapters that use the CommandType property of the command object, for NpgSql/PostgreSQL, you can just call the stored procedure using a select command:

using (NpgsqlCommand cmd = new NpgsqlCommand("select my_stored_proc()", conn))
{
    cmd.ExecuteNonQuery();
}

If you have parameters it follows the same pattern as any other command (select, insert, update):

using (NpgsqlCommand cmd = new NpgsqlCommand("select my_stored_proc(:P1, :P2)", conn))
{
    cmd.Parameters.AddWithValue("P1", "foo");
    cmd.Parameters.AddWithValue("P2", 3.14);
    cmd.ExecuteNonQuery();
}

You mentioned your parameter was an array... but I don't think you can have a Postgres array of mixed datatypes, can you? Certainly, in C# you can have an array of objects but I don't think that translates cleanly to a PostgreSQL array.

Here is an example of a parameter with an array, using an array of integers:

cmd.Parameters.Add(new NpgsqlParameter("NUMS", NpgsqlTypes.NpgsqlDbType.Array |
    NpgsqlTypes.NpgsqlDbType.Integer));
cmd.Parameters[0].Value = new int[3] { 1, 2, 3};

If you can add some detail to your question, perhaps I can frame the answer better.

answered Nov 17, 2018 at 19:24

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kanika Over a year ago

Thanks for your quick response. I have added the SP that I am using. Please see that. I have an object "Readings" which I have converted to an array.But you said this cannot be done with Postgres. So can I serialize the same to a JSON string then? And then split the string and use For loop in SP

Hambone Over a year ago

That's actually a good idea. If you pass the object as JSON, you should be able to work with it that way. In .NET it's very easy to convert an object to JSON. If you use the Newtonsoft.JSON package (via Nuget), the command is simply string json = JsonConvert.SerializeObject(myObject); Then you can pass that as a parameter to your Postgres procedure

kanika Over a year ago

Thanks for your reply. Also need to know how to use JSON string in stored procedure?

Collectives™ on Stack Overflow

To update multiple rows using stored procedure in Postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related