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!!! šŸ˜Ž

Categories: BlogSQL

Leave a Reply

Your email address will not be published. Required fields are marked *