Sample Header Ad - 728x90

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