How to add WHERE clause to a query

[kduraiswami]

I am trying to inject my own where clauses into a SQL statement

I can access the node here:  parsed_query.tree.stmts[0].stmt.select_stmt.where_clause  https://github.com/pganalyze/pg_query/blob/831173ca3b7b513963bf0cdab76b9aa3f8c2af79/lib/pg_query/filter_columns.rb#L59

I am trying to understand, is it always an :a_expr if it is one where_clause and a :bool_expr if it has an "AND"?

Am I correct that I would have to create my own new where_clause node this:

<PgQuery::Node.new 
    bool_expr: <PgQuery::BoolExpr.new boolop: :AND_EXPR 

And then put my own clauses in there using the and? Would there happen to be any code sample for creating a new where_clause? Thank you for the help!

[lfittl]

Yup, you got that right - typically you would have an a_expr in the WHERE clause if its a single expression, and a bool_expr if its multiple expressions. I don't have an example handy, but I think you're on the right track here.

If you want to know all the different variants, you could look at the gram.y file in Postgres, which all of this is based on. Specifically the a_expr rules would be relevant to understand the structure better:

https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/parser/gram.y#L14381

For example, the bool_expr gets created here via makeAndExpr:

https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/parser/gram.y#L14444 https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/parser/gram.y#L18111