How to execute postgresql script query in C# Npgsql

Question

I have a C# class that stores PostgreSQL queries in code like below:

 public static partial class DbQuery
 {
        public static string PoliciesInsert() =>
        @$"DO $$
            DECLARE policy_text varchar(100);

            BEGIN
                INSERT INTO common_sch.policies(policy, description)
                VALUES(@_Policy,@_Description)
                ON CONFLICT ON CONSTRAINT policies_pk
                DO UPDATE SET is_deleted = false
                RETURNING policy INTO policy_text;
    
            INSERT INTO common_sch.policy_to_role(role_id, policy, created_by, updated_by)
                SELECT 
                    unnest(@_Roles) as role_id, 
                    policy_text as policy, 
                    @_UserId as created_by, 
                    @_UserId as updated_by
            ON CONFLICT ON CONSTRAINT policy_to_role_unique
                DO UPDATE SET is_deleted = false, updated_by = @_UserId;
        END $$;";

        public static string select_test(string parms, string schema) => @$"SELECT * FROM {schema} ";

 }

And this PoliciesInsert query is executed like below:

  var parms = new
            {
                _Policy = "TEST_TEST_ACTION",
                _Description = "Testing the policies",
                _Roles = new int[] { 1, 2, 3, 4 },
                _UserId = 15
            };
  var result = await _dataManager.Insert<int>(MaxRavQuery.PoliciesInsert(), parms, CommandType.Text);

And this is how the DataManager singleton service's Insert method is implemented:

 public async Task<T> Insert<T>(string command, object parms, CommandType commandType)
        {
            T result;
            using (var dbConnection = await GetConnection())
            {
                using var transaction = await dbConnection.BeginTransactionAsync();
                try
                {
                    result = (await dbConnection.QueryAsync<T>(command, parms, transaction: transaction, 60, commandType)).FirstOrDefault();
                    transaction.Commit();

                }
                catch (Exception)
                {
                    await transaction.RollbackAsync();
                    throw;
                }
            }

            return result;
        }

And this is the error given when trying to execute this. 42703: column "_policy" does not exist I think because I'm using script keywords like DO and DECLARE this is failing. Moreover, it seems like it can't match the input parameters in the query.

If I can get a solution on how to run this as it is would be great. But other better solutions or suggestions are welcome!

score 1 · Accepted Answer · answered Mar 17 '23 at 15:15

1

You can't use parameters in a PostgreSQL anonymous DO block.

You can define the code as a stored function or procedure and invoke it with parameters. If you don't want to do that, you'll have to insert the parameter values as literals inside the DO block (make sure to sanitize them correctly if they originate from a user).

answered Mar 17 '23 at 15:15

Shay Rojansky

15,357
2
40
69

I wanted to remove the use of SPs/functions from PostgreSQL and move them to C# codebase so I can do source control easily. By 'define the code as a stored function or procedure and invoke it with parameters' you mean to create them in PostgreSQL level, right? It's not possible to do such thing in C# level, is it? – LordDraagon Mar 17 '23 at 17:20
If you want to, you can create the PostgreSQL SP/function in C# code, execute it (with your parameters), and then drop it. But that begs the question: why use PG code at all? Your code above in the DO block seems like you could just execute it directly; it's just two INSERTs batched together. – Shay Rojansky Mar 19 '23 at 09:22
I agree creating PostgreSQL SP/function in C# code will not be good. For now, I have changed my code to execute the 2 insert queries without the `DO` block but the reason I used a `DO` block in the first place was to declare a variable to store the result of the first insert and reuse it in the next insert query. For this case I could do without the `policy_text` variable but what if there are cases I couldn't, so in that case would be better to move those specific queries to PostgreSQL or run separate queries in C#? Any other suggestion? Thanks! – LordDraagon Mar 19 '23 at 13:24
Just to add something for others, you can use `WITH CTE` to hold the value of the first insert. And the CTE variable's value is only available for use in subsequent query. It will be out of scope for other below statements. – LordDraagon Mar 19 '23 at 15:13
1

It's true that you may be able to combine your INSERTs using a CTE to pass the INSERT results to a subsequent query. Otherwise, you'll need to either read the results of your INSERTs in .NET and then send the subsequent query separately, using that data; the main disadvantage there is the extra database roundtrip. If that really isn't acceptable, you'll need a stored function/procedure. – Shay Rojansky Mar 21 '23 at 14:54
[link](https://stackoverflow.com/questions/35248217/how-to-use-multiple-ctes-in-a-single-sql-query) Found a way to chain `WITH CTE` – LordDraagon Apr 18 '23 at 18:06

How to execute postgresql script query in C# Npgsql

1 Answers1