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!

Answer 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).