Problem with case statement and dynamic table name

Question

I have the following case statement

case %1$s::text
  when ''lookup_relation_job'' then
    case %1$s.relation
      when ''followers'' then ''get-followers''
      when ''following'' then ''get-following''
      when ''blocking'' then ''get-blocking''
      when ''muting'' then ''get-muting''
    end
  when ''manage_list_members_job'' then
    case %1$s.add
      when true then ''add-list-member''
      else ''remove-list-member''
    end
  when ''manage_relation_job'' then
    case %1$s.relation
      when ''follow'' then
        case %1$s.add
          when true then ''add-follow''
          else ''remove-follow''
        end
      when ''block'' then
        case %1$s.add
          when true then ''add-block''
          else ''remove-block''
        end
      when ''mute'' then
        case %1$s.add
          when true then ''add-mute''
          else ''remove-mute''
        end
    end
end

Here %1$s is an argument of type regclass.

When I run the above with %1$s = lookup_relation_job, Postgres throws an error saying: column lookup_relation_job.add does not exist. But it shouldn't even check for the add column when the argument is lookup_relation_job according to the case logic. What's going wrong here?

I suspect either there's some kind of fall-through happening here (I double checked that I've closed all case statements with end), or Postgres checks for all the conditions even if the parent condition is false (this is unlikely).

Any help would be appreciated!

Edit: Below is the whole relevant PL/pgSQL function

create or replace function get_jobs_to_add
  (job_table regclass, active_jobs bigint[], failed_jobs bigint[], out result bigint[])
  as $$
begin
execute format('
  select array(
    select
    distinct on (%1$s.user_id)
    %1$s.id
  from %1$s
    left join user_profile on user_profile.id = %1$s.user_id
    left join twitter_api_rate_limit on
      twitter_api_rate_limit.user_twitter_id = user_profile.twitter_id and
      twitter_api_rate_limit.endpoint = (
        case %1$s::text
          when ''lookup_relation_job'' then
            case %1$s.relation
              when ''followers'' then ''get-followers''
              when ''following'' then ''get-following''
              when ''blocking'' then ''get-blocking''
              when ''muting'' then ''get-muting''
            end
          when ''manage_list_members_job'' then
            case %1$s.add
              when true then ''add-list-member''
              else ''remove-list-member''
            end
          when ''manage_relation_job'' then
            case %1$s.relation
              when ''follow'' then
                case %1$s.add
                  when true then ''add-follow''
                  else ''remove-follow''
                end
              when ''block'' then
                case %1$s.add
                  when true then ''add-block''
                  else ''remove-block''
                end
              when ''mute'' then
                case %1$s.add
                  when true then ''add-mute''
                  else ''remove-mute''
                end
            end
        end
      )
  where %1$s.finished = false
    and %1$s.paused = false
    and %1$s.deleted = false
    and (twitter_api_rate_limit.resets_at is null or twitter_api_rate_limit.resets_at < now())
    and not (%1$s.id = any(%3$L))
    and %1$s.user_id not in (
      select user_id from %1$s where id = any(%2$L)
    )
  order by %1$s.user_id, %1$s.priority desc
);', job_table, active_jobs, failed_jobs) into result;

end
$$ language plpgsql;

Answer 1

After the parameter has been substituted, the SQL statement will look like

...
case 'mytable'::text
  when 'lookup_relation_job' then
    case mytable.relation
...

When this statement is parsed (before it is executed), mytable.relation gets resolved and throws an error. Consider this note in the documentation:

As described in Section 4.2.14, there are various situations in which subexpressions of an expression are evaluated at different times, so that the principle that “CASE evaluates only necessary subexpressions” is not ironclad. For example a constant 1/0 subexpression will usually result in a division-by-zero failure at planning time, even if it's within a CASE arm that would never be entered at run time.

You will probably have to split this up into several queries.