Multiple THEN outputs on a PostgresQL query

Question

Helllo there, I've been playing around with a materialised view, and my goal is to pre-process as much information as I can, aggregating the info into this view that would speed up the data analysis.

As a such, I'd like to use a CASE expression with an input condition and two assignments if the condition is satisfied.

Something like

SELECT 
 CASE 
 WHEN t."page" ~* '...' THEN t.type = 'Product', t.something = 'something else here'
END AS "page_type"

Reading through the documentation, but also checking for several examples I couldn't see this doable. Am I missing something?

Assuming is not, the only thing I can think of is using a function that returns an array or something similar. Would that be an a possible approach? If so, any example I can be pointed out to learn a decent DDL pattern?

Thanks

UPDATE To support @a_horse_with_no_name answer

SELECT * 
FROM "V_2019年06月10日_to_2019年06月20日_GSC" t,
 (
 SELECT
 Regexp_replace(t."page", '...', '1円') as node
 FROM "V_2019年06月10日_to_2019年06月20日_GSC" i
 WHERE i.page = t.page
 ) AS "IFs"

The above generates an error in referencing the t table. If I go for a CROSS JOIN LATERAL, the query works, but then the GROUP BY I have at the end of the outer query cannot dedup anymore the results

UPDATE 2

Here's a simplification of the query. It's of course much more complex than this, but hopefully it suffice.

SELECT -- t."page", 
 "URL".node, t.area, "URL".page_type,
 CASE
 WHEN "URL".page_type = 'ABC' THEN <here I need my value>
 ELSE ''
 END AS "Node_Type",
 FROM "V_2019年06月10日_to_2019年06月20日_GSC" t
 CROSS JOIN LATERAL (
 SELECT 
 Regexp_replace(t."page", '...', '1円2円3円4円5円6円') as node, 
 CASE 
 WHEN t."page" ~* '...' THEN 'ABC'
 WHEN t."page" ~* 'filter' THEN 'DEF' 
 ELSE 'Other'
 END AS "page_type" 
 ) AS "URL"
 GROUP BY "URL".node, t.area, "URL".page_type
;

A URL processed by this query could look like this /level1/level2/level3/?filter=123

Idealy I'd like to:

• capture level1/level2/level3 with the Regexp_replace, and I have the regexpr working for this.
• I need to identify once again whether the filter is included in the page to populate the page_type, and the case above should be ok.
• capturing 123 as part of the outer CASE "Node_Type", but I cannot get this done, hence my first thought of a CASE processing two outputs.

Is there an effective way to get this out?

Answer 1

A single case expression can only return a single value (=expression). So yes, you are right you can't do that. If you want to have two output columns, you need two case expressions.

SELECT CASE 
 WHEN t.page ~* '...' THEN 'Product here'
 ... other outputs ....
 END AS page_type, 
 CASE 
 WHEN t.page ~* '...' THEN 'something else'
 ... other outputs ....
 END AS something _else
FROM ...

If you don't want to repeat the condition, you could wrap that into a derived table:

SELECT node, area, page_type, 
 CASE
 WHEN page_type = 'ABC' THEN 'Product'
 WHEN page_type = 'DEF' THEN 'Something else'
 ELSE ''
 END AS "Node_Type"
from (
 SELECT area, 
 Regexp_replace(t."page", '...', '1円2円3円4円5円6円') as node, 
 CASE 
 WHEN t.page ~* '...' THEN 'ABC'
 WHEN t.page ~* 'filter' THEN 'DEF' 
 ELSE 'Other'
 END AS page_type, 
 string_to_array(trim(substr(page, 1, strpos(page, '?')-1),'/'), '/') as levels,
 regexp_replace(page, '(^.*\?filter=)([0-9]+)', '2円', 'g') as filter_value
 length(t.page) as string_length
 FROM "V_2019年06月10日_to_2019年06月20日_GSC" 
) t;

The expression string_to_array(trim(substr(page, 1, strpos(page, '?')-1),'/'), '/') will return the individual elements (levels) of the URL as array elements. '/level1/level2/level3/?filter=123' will yield and array {level1,level2,level3) and '/level1/level2/level3/level4/?filter=123' will return 4 elements. If you need to access the number of levels in the outer select, you can use e.g. cardinality(levels)

The expression regexp_replace(page, '(^.*\?filter=)([0-9]+)', '2円', 'g') will return the value of the parameter filter as a single expression (It's probably not 100% fool-proof, but URL parsing with regex is not a simple task).

• Andrea Moro: please see above. I've added a simplified query to help understanding the context. Is this enough?
• Andrea Moro: I have done that because I have in reality two cross lateral, one of them to calculate the string length, and which is also used by the first case. I've stripped that out to concentrate on this problem specifically.
• user1822: @AndreaMoro: I don't see how you need a cross join at all. Just do all that in the derived table.
• Andrea Moro: a_horse_with_no_name, I need that because the length is in reality calculated on the "node". This is because I do some string manipulation that is affecting the length. In fact, I am after counting the number of "/" which in the t.page is much longer and it will not portrait the right figure. With the regexpr replacement, I'm factoring some changes, that's why I opted that way.
• user1822: @AndreaMoro: if that is used to get the number of elements in the URL, see my edit. This can be done simpler by parsing the URL into an array.