Does Postgresql plpgsql/sql support short circuiting in the where clause?

According to the Postgresql docs and this answer by Tom Lane, the order of execution of WHERE constraints is not reliable.

I think your best bet here may be to add that other part of your WHERE clause into the top of your function and "fail fast"; ie, run my_id in ( SELECT my_other_id FROM my_other_tables) in your function, and if it doesn't pass, return right there before doing you intensive processing. That should get you about the same effect.

As documented, the evaluation order in a WHERE clause is supposed to be unpredictable.

It's different with subqueries. With PostgreSQL older than version 12, the simplest and common technique to drive the evaluation order is to write a subquery in a CTE. To make sure that the IN(...) is evaluated first, your code could be written as:

WITH subquery AS
(select * from my_tables
  WHERE my_id in (SELECT my_other_id FROM my_other_tables)
)
SELECT * FROM subquery
  WHERE some_slow_func(arg) BETWEEN 1 AND 2;

Starting with PostgreSQL version 12, WITH subqueries may be inlined by the optimizer (see the doc page on WITH queries for all the details), and the non-inlining is only guaranteed when adding the MATERIALIZED clause:

WITH subquery AS MATERIALIZED
(select * ... the rest is similar as above)

---

Something else that you may tweak is the cost of your function to signal to the optimizer that it's slow. The default cost for a function is 100, and it can be altered with a statement like:

ALTER FUNCTION funcname(argument types) cost N;

where N is the estimated per-call cost, expressed in an arbitrary unit that should be compared to the Planner Cost Constants.

I know this is an old question, but recently ran into similar issue, and found using a CASE predicate in the WHERE clause worked better for me. In the context of the answer above:

SELECT *
  FROM my_tables
 WHERE CASE WHEN my_id in (SELECT my_other_id
                             FROM my_other_tables)
           AND some_slow_func(arg) BETWEEN 1 AND 2
           THEN 1 
           ELSE 0 
      END = 1;

This makes for SQL that is slightly more DB agnostic. Of course, it may not make use of indexes if you have some on my_id, but depending on the context you're in, this could be a good option.