'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 |
|---|
