NpgSql - How to pass parameter into DO block in PostgreSQL query
4
votes
2
answers
2384
views
I am trying to use a parameter passed into a PostgreSQL query inside a DO block within that query. It fails though, and seems to be parsing the statement as if the parameter was a column. Is it possible to reference a query parameter in this way?
Here is my C# code (using Npgsql and Dapper as well):
private static async Task ParameterInDoBlock(IDbConnection postgresConn)
{
DynamicParameters queryParams = new DynamicParameters();
queryParams.Add("some_variable", 123);
//example of successfully using a parameter
string sql = @"SELECT @some_variable;";
var row = await postgresConn.QuerySingleOrDefaultAsync(sql, queryParams);
Console.WriteLine($"Simple select: {row}");
//this one unexpectedly fails
sql = @"
DO
$$
BEGIN
IF @some_variable = 1 THEN
RAISE EXCEPTION 'blah';
END IF;
END
$$;";
await postgresConn.QuerySingleOrDefaultAsync(sql, queryParams);
/*
Exception data:
Severity: ERROR
SqlState: 42703
MessageText: column "some_variable" does not exist
InternalPosition: 2
InternalQuery: @some_variable = 1
Where: PL/pgSQL function inline_code_block line 3 at IF
File: parse_relation.c
Line: 3633
Routine: errorMissingColumn
*/
}
Asked by Anssssss
(248 rep)
Jan 23, 2023, 11:38 PM
Last activity: Jan 24, 2023, 01:58 PM
Last activity: Jan 24, 2023, 01:58 PM