CODEWITHSUNDEEP
postgresql
nachocab
nachocab

Reputation: 14454

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

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.

Upvotes: 0

Views: 102

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248245

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;

Upvotes: 1

Related Questions