Sample Header Ad - 728x90

Problem with case statement and dynamic table name

1 vote
1 answer
352 views
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;
Asked by Sumit Ghosh (111 rep)
Jan 1, 2023, 06:23 PM
Last activity: Jan 1, 2023, 07:15 PM