为什么Trim和子字符串在此PL/PGSQL函数中不起作用？
我编写了此非常简单的PL/PGSQL函数，以便在数据库中管理日期格式（DateTuple是一种自定义类型是INT的元组）：
CREATE FUNCTION date2datetuple (_date text)
RETURNS datetuple AS
$BODY$
BEGIN
IF _date ~ '[0-9]{4}' THEN
RETURN (_date::int,
_date::int);
ELSIF _date ~ '[0-9]{4}ca' THEN
RETURN (trim(trailing 'ca' from _date)::int,
trim(trailing 'ca' from _date)::int);
ELSE
RETURN (substring(_date from '^[0-9]{4}')::int - substring(_date from '[0-9]{2}$')::int,
substring(_date from '^[0-9]{4}')::int + substring(_date from '[0-9]{2}$')::int);
END IF;
END;
$BODY$
LANGUAGE plpgsql;
该函数在没有任何错误的情况下被定义，但是当我输入EG '1980CA'时然后给我带来了这个错误：
ERROR: invalid input syntax for type integer: "1950ca"
CONTEXT: PL/pgSQL function date2datetuple(text) line 4 at RETURN
SQL state: 22P02
在进行进一步调查后，任何参数似乎都不会被修改，这会引发错误。为什么功能不做任何事情？
I wrote this very simple PL/pgSQL function in order to manage date formats in my database (datetuple is a custom type being a tuple of int's):
CREATE FUNCTION date2datetuple (_date text)
RETURNS datetuple AS
$BODY$
BEGIN
IF _date ~ '[0-9]{4}' THEN
RETURN (_date::int,
_date::int);
ELSIF _date ~ '[0-9]{4}ca' THEN
RETURN (trim(trailing 'ca' from _date)::int,
trim(trailing 'ca' from _date)::int);
ELSE
RETURN (substring(_date from '^[0-9]{4}')::int - substring(_date from '[0-9]{2}
The function gets defined without any error, but when I input e.g. '1980ca' then it throws me this error:
ERROR: invalid input syntax for type integer: "1950ca"
CONTEXT: PL/pgSQL function date2datetuple(text) line 4 at RETURN
SQL state: 22P02
Upon further investigation it seems that any argument just gets passed on without being modified, which throws an error since '1950ca' cannot be cast as int. Why are the functions not doing anything?
)::int,
substring(_date from '^[0-9]{4}')::int + substring(_date from '[0-9]{2}
The function gets defined without any error, but when I input e.g. '1980ca' then it throws me this error:
Upon further investigation it seems that any argument just gets passed on without being modified, which throws an error since '1950ca' cannot be cast as int. Why are the functions not doing anything?
)::int);
END IF;
END;
$BODY$
LANGUAGE plpgsql;
The function gets defined without any error, but when I input e.g. '1980ca' then it throws me this error:
Upon further investigation it seems that any argument just gets passed on without being modified, which throws an error since '1950ca' cannot be cast as int. Why are the functions not doing anything?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据
您应该在第一个和（可能）的第二个比较中锚定模式：
According to the documentation:
You should anchor the pattern in the first and (probably) second comparison: