从 URL 中提取 HTTP 查询参数的 PostgreSQL 函数

Question

我想从表示 URL 的字符串中提取查询参数，并且我想在存储的函数中执行此操作(我是否可以使用某些标准函数？)。

在 Python 中，这将是:

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]

我目前在 plpgsql 中的尝试是:

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;

如果 oid 是查询字符串中的最后一个参数并且至少有一个前导参数(否则我还需要识别 ?oid= );但是，当后面有另一个 & 时，它会失败。我想在这里安全......

endpos 变量似乎有问题。

谁能教教我？谢谢!

我需要它来使用 PostgreSQL 9.3+。

编辑:

我发现了我的逻辑错误(当然我需要减去 5 而不是 1，愚蠢的我)，但是在马的回答之后，我的函数看起来像这样:

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

Answer 1

除了在 Python function 中使用您的 Python 代码外，我会为此使用正则表达式:

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

substring(link from 'oid=\w+') 将返回 oid=abc123，然后 split_part() 将提取第二个元素使用 = 作为分隔符。

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;

将返回:

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

我认为这也适用于 9.3