How to use a query parameter in DO statement in Postgresql?

Question

I was trying to use a query parameter in DO statement.

But sample code below is failed with exception.

Message=42703: column "param" does not exist

          using (var conn = new NpgsqlConnection(cs)) {
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(new NpgsqlParameter("@param", 200));
                cmd.CommandText = @"
                DO $$ 
                    DECLARE _temp_val integer := @param;
                BEGIN       

                    -- do something                    
                    
                    CREATE TEMP TABLE TEMP_TB(
                        VALUE1 INTEGER
                    );

                    INSERT INTO TEMP_TB (VALUE1) VALUES (_temp_val);    

                END $$;

                SELECT * FROM TEMP_TB;
                ";

                var result = cmd.ExecuteScalar();
            }

On the other hand, sample code below succeeded with no exception.

          using (var conn = new NpgsqlConnection(cs)) {
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(new NpgsqlParameter("@param", 200));
                cmd.CommandText = "select @param;";

                var result = cmd.ExecuteScalar();
            }

How can I fix errors? Thanks.

Answer 1

You cannot use parameters in a DO statement.

Either construct the query string on the client side (beware of SQL injection!) or (better) write a function in the database and pass a parameter.