NpgSql - How to pass parameter into DO block in PostgreSQL query

Question

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 = @&quot;SELECT @some_variable;&quot;;
var row = await postgresConn.QuerySingleOrDefaultAsync&lt;dynamic&gt;(sql, queryParams);
Console.WriteLine($&quot;Simple select: {row}&quot;);

//this one unexpectedly fails
sql = @&quot;
DO
$$
BEGIN
    IF @some_variable = 1 THEN
        RAISE EXCEPTION 'blah';
    END IF;
END
$$;&quot;;
await postgresConn.QuerySingleOrDefaultAsync&lt;dynamic&gt;(sql, queryParams);
/*
Exception data:
 Severity: ERROR
 SqlState: 42703
 MessageText: column &quot;some_variable&quot; 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
*/

}

score 3 · Accepted Answer · answered Jan 24 '23 at 03:47

Parameters can only be used in SELECT, INSERT, UPDATE and DELETE. I can think of two solutions:

write a database function in PL/pgSQL, have the parameter be a function parameter and use a parameterized statement in a SELECT statement that calls the function
compose a string that contains the actual value instead of a parameter and execute that (but take care that your code is not vulnerable to SQL injection)

score 2 · Answer 2 · answered Jan 24 '23 at 13:58

@Laurenz-Albe, thanks for the answer. I liked the first option better (prefer parameterized queries), so here is what I would end up with:

sql = @"
--create temp function to hold code
CREATE OR REPLACE FUNCTION pg_temp.some_function(x int) RETURNS VOID
LANGUAGE plpgsql
AS
$$
BEGIN
    --just the example code I was trying to do before
    IF x = 1 THEN
        RAISE EXCEPTION 'blah';
    END IF;
END
$$;
SELECT pg_temp.some_function(@some_variable);
";
await postgresConn.QuerySingleOrDefaultAsync<dynamic>(sql, queryParams);

NpgSql - How to pass parameter into DO block in PostgreSQL query

2 Answers2