Does a CASE statement lose alias scope in PostgreSQL?

Question

First of all, this SQL works:

select
    case
        when s.luserid > 0 then u.szusername
        when s.lgroupid > 0 then g.szgroup
        when s.lldapid > 0 then 'LDAP Group'
    end as name
from security s
left join users u on s.luserid = u.id
left join usergroups g on s.lgroupid = g.id
order by name

The above block is proving ordering by alias name works, as well as declaring alias name works while the term name is a reserved word, which is not relevant to the question

My issue happens when I make a case statement with an alias in it:

Pay attention to the alias useid

select
    case
        when sa.luserid > 0 then sa.luserid
        when sa.lgroupid > 0 then sa.lgroupid
        when sa.lldapid > 0 then sa.lldapid
    end as useid,
from security s
left join users u on s.luserid = u.id
left join usergroups g on s.lgroupid = g.id
order by
        case
            when 'user selection' = 'all objects by user' then useid
            else s.lobjectid
        end

The text user selection is replaced by a parser with literal text before the SQL is run. Both the alias useid and s.lobjectid are type bigint.

An error is thrown at when 'user selection' = 'all objects by user' then useid.

Am I losing scope of the alias useid within the CASE statement? Why does this fail when I try to use the alias useid here.

By the way, this SQL works as well:

select
    case
        when s.luserid > 0 then u.szusername
        when s.lgroupid > 0 then g.szgroup
        when s.lldapid > 0 then 'LDAP Group'
    end as name
from security s
left join users u on s.luserid = u.id
left join usergroups g on s.lgroupid = g.id
order by
        case
            when s.lobjectid > 0 then s.lobjectid
            else s.luserid
        end

The above block is proving that a CASE statement within an ORDER BY statement does work. All debates over the logical operations of the above block of SQL is irrelevant to the question, for it is simply junk example SQL.

Answer 1

What you are trying to do is not possible in Postgresql since it doesn't allow you to use an ALIAS within the same query as a field. Different from Mysql where you can do it.

To solve your problem you either create your query as a subquery and then your alias will be a field therefore can be used as:

select useid, lobjectid from (
  select
      case
          when sa.luserid > 0 then sa.luserid
          when sa.lgroupid > 0 then sa.lgroupid
          when sa.lldapid > 0 then sa.lldapid
      end as useid,
      lobjectid
   from security s
  left join users u on s.luserid = u.id
  left join usergroups g on s.lgroupid = g.id
  ) as t
order by
        case
            when 'user selection' = 'all objects by user' then useid
            else lobjectid
        end

Or you can repeat the entiry case block

  select
      case
          when sa.luserid > 0 then sa.luserid
          when sa.lgroupid > 0 then sa.lgroupid
          when sa.lldapid > 0 then sa.lldapid
      end as useid,
      lobjectid
   from security s
  left join users u on s.luserid = u.id
  left join usergroups g on s.lgroupid = g.id
order by
        case
            when 'user selection' = 'all objects by user' then 
                  case
                      when sa.luserid > 0 then sa.luserid
                      when sa.lgroupid > 0 then sa.lgroupid
                      when sa.lldapid > 0 then sa.lldapid
                  end
            else lobjectid
        end

Some engines will let you use the order number of the field on the select scope to the order by like:

select a, b, c from sometable order by 1, 2

Which means that this query will be ordered by the fields a and b