[statictype]

I tried to build a T-Sql-to-pgsql compiler to enable us to migrate our code but ran into some fundamental issues.

Sql Server allows you to have arbitrary statements/declarations embedded in your sql queries. It also doesn't require type information to be specified in many places.

How does this translator get around that?

For example, if I have this bit of unoptimized T-Sql:

   declare @m int
   select @m=[MeterID] from EnergyMeters where MeterLocation='/a/b/c';

   select sum([Value]) from EnergyData where [MeterID]=@m;

How would this get translated to pgsql? (Yes, you can combine this specific statement into a single query - this is a trivial example to highlight the point)

[dragonwriter]

If you are just binding variables with early (before the last one that returns data) selects like that, just turning them into subqueries or factoring them out to CTEs works, which should be reasonably straightforward mechanically.