CODEWITHSUNDEEP
sqlpostgresqlparsingurl
Josef Joe Samanek
Josef Joe Samanek

Reputation: 1704

Postgresql parse url parameters

The following is how I currently parse parameters from an url query string. Does anyone know of a nicer and faster solution? Thx.

select
  id,
  url,
  split_part(regexp_split_to_table((regexp_matches(url, '\?(.*)'))[1], '&'), '=', 1) as key,
  split_part(regexp_split_to_table((regexp_matches(url, '\?(.*)'))[1], '&'), '=', 2) as value
from ad;

Upvotes: 0

Views: 3806

Answers (1)

9000
9000

Reputation: 40894

You do two regexp matches for the same URL, and then two splits of the result.

I'd suggest using a simpler function to find the position of the first ? in the URL, e.g. substring(url, position('?' in url)), then you likely can use regexp_split_to_array to do the splitting only once.

This can happen in a common table expression or a subquery; then you can access the array results by index to return.

This, of course, only makes sense if the query is dominated by the processing time, and not filtering and fetching time. Optimization without profiling is a waste of time.

Upvotes: 1

Related Questions