PostgreSQL function to extract a HTTP query argument from a URL

Question

I'd like to extract a query argument from a string which represents a URL, and I'd like to do it in a stored function (there is not by chance some standard function I could use?).

In Python, this would be:

from urlparse import urlparse, parse_qs
def extract_oid(url):
    """
    extract the 'oid' query argument

    (simplified, no error handling)

    >>> extract_oid('http://some.host/some/path?oid=abc123&other')
    'abc123'
    """
    return parse_qs(urlparse(url).query)['oid'][0]

My current try in plpgsql is:

CREATE OR REPLACE FUNCTION extract_oid (link text)
RETURNS text
AS $$
DECLARE
  pos1 integer := position('&oid=' in link);
  tail text := substring(link from pos1 + 1);
  endpos integer := position('&' in tail);
BEGIN
  if link is NULL or pos1 = 0 then
    RETURN NULL;
  ELSIF endpos = 0 then
    RETURN substring(tail from 5);
  ELSE
    RETURN substring(tail from 5 for endpos - 1);
  END IF;
END;
$$ LANGUAGE plpgsql;

This works fine if the oid is the last argument in the query string and has at least one predecessor (otherwise I'd need to recognize ?oid= as well); however, it fails when there is another & following. I'd like to be sefe here ...

Something seems to be wrong with the endpos variable.

Can someone please enlighten me? Thank you!

I need this to work with PostgreSQL 9.3+.

Edit:

I found my logical error (of course I needed to subtract 5 rather than 1, silly me), but after the horse's answer, my function looks like this:

CREATE OR REPLACE FUNCTION extract_oid (url text)
RETURNS text
AS $$
BEGIN
  RETURN split_part(substring(url from '[?&]oid=[^&]+'), '=', 2);
END;
$$ LANGUAGE plpgsql;

user330315 · Accepted Answer · 2017-11-07 12:00:58Z

5

Apart from using your Python code in a Python function, I would use a regular expression for this:

split_part(substring(link from 'oid=\w+'), '=', 2)

substring(link from 'oid=\w+') will return oid=abc123 and split_part() will then extract the second element using = as the delimiter.

with t (url) as (
  values 
    ('http://some.host/some/path?oid=abc123&other'), 
    ('http://some.host/some/path?other&oid=def456&foo=bar')
)
select split_part(substring(url from 'oid=\w+'), '=', 2)
from t;

will return:

split_part
----------
abc123    
def456

I think that should work on 9.3 as well

answered Nov 7, 2017 at 12:00

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Tobias Over a year ago

Yes, thank you! I changed the from expression to '[?&]oid=[^&]+' to ignore otherprefixoid vars and allow non-word characters as well.

Collectives™ on Stack Overflow

PostgreSQL function to extract a HTTP query argument from a URL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related