How to simplify nested function calls and a case statement in postgres?

Question

I currently have this fragment as part of a larger query. It works but I'd like to simply the nested string functions and the duplicated substring calls within the CASE statement.

SELECT
  split_part(
    convert_from(
      decode(
        CASE WHEN char_length(substring(pr.modifications, 'eid=([^&]+)')) IN (74,50) THEN
          substring(pr.modifications, 'eid=([^&]+)') || '=='
        ELSE
          substring(pr.modifications, 'eid=([^&]+)')
        END, 'base64'), 
      'UTF8'), 
    ' ', 1) AS calendar_event_id;

Here is a test case:

SELECT
  split_part(
    convert_from(
      decode(
        CASE WHEN char_length('ZmJ0OXQyMTQwa2V0Z3Y5bjZra2I4amVzZjggOHU0ZDc2Zmk5Z2wyc3RxMnQ4a2hrcGd0MWNAZw') IN (74,50) THEN
          'ZmJ0OXQyMTQwa2V0Z3Y5bjZra2I4amVzZjggOHU0ZDc2Zmk5Z2wyc3RxMnQ4a2hrcGd0MWNAZw' || '=='
        ELSE
          'ZmJ0OXQyMTQwa2V0Z3Y5bjZra2I4amVzZjggOHU0ZDc2Zmk5Z2wyc3RxMnQ4a2hrcGd0MWNAZw'
        END, 'base64'), 
      'UTF8'), 
    ' ', 1) AS calendar_event_id;
-- Output:
-- fbt9t2140ketgv9n6kkb8jesf8

I've tried several alternatives, but all I get are syntax errors.

Laurenz Albe · Accepted Answer · 2020-10-21 10:55:40Z

1

Try a CTE:

WITH mystr AS (
   SELECT substring(pr.modifications, 'eid=([^&]+)') AS s
)
SELECT
  split_part(
    convert_from(
      decode(
        CASE WHEN char_length(s) IN (74,50) THEN
          s || '=='
        ELSE
          s
        END, 'base64'), 
      'UTF8'), 
    ' ', 1) AS calendar_event_id
FROM mystr;

answered Oct 21, 2020 at 10:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to simplify nested function calls and a case statement in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related