1

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.

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.