1

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;
5
  • 1
    Is that really a PL/pgSQL CASE statement or a CASE expression that is part of a SQL query?
    – user1822
    Jan 1, 2023 at 18:51
  • It's inside an EXECUTE format() statement of PL/pgSQL function @a_horse_with_no_name Jan 1, 2023 at 19:02
  • 1
    Please edit your question and show the whole code in the context of the function. As written, it doesn't make sense as a statement (because no PL/PgSQL code is executed), only as an expression that could be used to store the result into a variable.
    – user1822
    Jan 1, 2023 at 19:05
  • Makes sense, added the whole function as an edit to the OP. Jan 1, 2023 at 19:07
  • Well, that's a CASE expression (in SQL)
    – user1822
    Jan 1, 2023 at 19:27

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.