Обсуждение: PGSQL "macro" or "inplace subfunction"?
Dear Members!
Many times I need to type same expressions in SQL queries.
select [exprs] from anytable
where [exprs] > 0
order by [exprs]
Sometimes I solve this with subquery, or "WITH" query, because it is easier to define value once, and use it more times...
select * from (
select [exprs] as e1 from anytable
) t where e1 > 0
order by e1
(But I can't do this with group by)
Sometimes the expression is a repeated code which I don't want to create a persistent function, but I need to use more times.
Pseudo code:
macro _Sgn(aCol1, aCol2):
macro _Sgn(aCol1, aCol2):
case
when(aCol1 = aCol2) then 0
when (aCol1 < aCol2) then -1
when (aCol1 > aCol2) then 1
else NUL
end
select
_Sgn(X1, X2) as XDiff,
_Sgn(Y1, Y2) as YDiff,
_Sgn(Z1, Z2) as ZDiff
_Sgn(Y1, Y2) as YDiff,
_Sgn(Z1, Z2) as ZDiff
...
This changed by the compiler to...
This changed by the compiler to...
select
case
when(X1 = X2) then 0
when (X1 < X2) then -1
when (X1 > X2) then 1
else NUL
end
...
case
when(Y1 = Y2) then 0
...Do you know same thing in PGSQL queries?
Thank you for any help!
Best regards
dd