One thing that you may need to use from time to time in Postgres is a function to be able to extract the value of a URL parameter from URLs stored in your DB. I wanted to be able to do this too so I wrote the following function:
If you have run the script shown above you should now have access to the new get_url_params(…) (AKA public.get_url_params(…)) function.
How To Use It
Let’s say that you have the following URL stored in your DB:
http://example.com/?q=search+term&utm=stuff&x=Hell%C3%B6%20World%21
The above URL contains 3 different parameters. In order to get them you can execute this query:
SELECT *
FROM get_url_params('http://example.com/?q=search+term&utm=stuff&x=Hell%C3%B6%20World%21');Executing the above SQL will result in the following table output:
| param_number | raw_key | raw_value | key | value |
|---|---|---|---|---|
| 1 | q | search+term | q | search term |
| 2 | utm | stuff | utm | stuff |
| 3 | x | Hell%C3%B6%20World%21 | x | Hellö World! |
Of course if you just wanted to get the value of a specific parameter (eg. x), you can add the name of it to the WHERE clause:
SELECT *
FROM get_url_params('http://example.com/?q=search+term&utm=stuff&x=Hell%C3%B6%20World%21')
WHERE "key" = 'x';Feel free to use this function for your projects. Happy coding!!! 😎
