'How to pass a command parameter to complex Postgres SQL query with declared variables using Dapper

I'm trying to execute this PostgreSql parameterized query using Dapper in dotnet Core app. The issue is that Postgres doesn't recognize parameter @p_instruction. It says that column "p_instruction" does not exist, but it is not a column - it is the query parameter.

Do you know if it is even possible to execute such a query? If yes then how?

Table Action on which I execute query:

Action
  Id integer,
  Type integer,
  Name text,
  Instruction text,
  ValidationParameters text,
  Settings text,
  Timeout integer,
  DataSourceTypeId integer,
  Description text,

PostgreSql query:

do $$
declare
    actionid integer;
    actionname text;
    actiondesc text;

begin

    actionid := (select max("Id")+1 from "Actions");
    actionname := (array_to_string(array(select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(62-1)+1)::integer),1) from generate_series(1,35)),''));
    actiondesc := (array_to_string(array(select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(62-1)+1)::integer),1) from generate_series(1,35)),''));

    insert into "Actions" values (actionid, 1, actionname, @p_instruction, null, null, null, 15, actiondesc);

end $$

c# code looks like:

await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p_instruction", "some value");
var sqlQuery = ""; // here I pass PostgresSql query from the example above

var command = new CommandDefinition(sqlQuery, dynamicParameters);
await connection.ExecuteAsync(commandDefinition);

The result of execution is an error:

Npgsql.PostgresException (0x80004005): 42703: column "p_instruction" does not exist
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
         at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken) in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 311
        Exception data:
          Severity: ERROR
          SqlState: 42703
          MessageText: column "p_instruction" does not exist
          Hint: Perhaps you meant to reference the column "Actions.Instruction".
          InternalPosition: 57
          InternalQuery: insert into "Actions" values (actionid, 1, actionname, @p_instruction, null, null, null, 15, actiondesc)
          Where: PL/pgSQL function inline_code_block line 13 at SQL statement
          File: parse_relation.c
          Line: 3599
          Routine: errorMissingColumn


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source