PostgreSQL - 整数的无效输入语法

Question

我创建了名为 t_user_type 的复合类型:

CREATE TYPE t_user_type AS
   (uid integer,
    firstname character varying,
    lastname character varying,
    companyname character varying,
    email character varying,
    sip_phone integer);

...我需要将字符串转换为该类型，所以我这样做了

SELECT '(11423, FirstName, LastName, Company, email@gmail.com, 204)' :: t_user_type;

一切都很好。没有错误，什么都没有。但是当我使用过程做同样的事情时，我得到了这个错误:Invalid input syntax for integer: "(11423,"FirstName","LastName","Company","email@gmail.com", 204) “。

这是我的程序:

CREATE OR REPLACE FUNCTION change_type(p_user character varying)
  RETURNS void AS
$BODY$DECLARE
  v_user_type t_user_type;
BEGIN
  SELECT p_user :: t_user_type INTO v_user_type;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION change_type(character varying)
OWNER TO postgres;

这里是使用过程的查询:

SELECT change_type(
   '(11423, FirstName, LastName, Company, email@gmail.com, 204)');

谁能告诉我我做错了什么？

Answer 1

不确定为什么会失败我做了一些小改动，我只是将值转换为您的局部变量并且它似乎可以工作。但我还不知道为什么。 SELECT INTO 变量是否像您所做的那样合法(我想我可以将常规文本字符串转换为文本变量)？当它只是一个没有源表的变量时，我通常会像下面的示例那样做。

CREATE TYPE t_user_type AS
   (uid integer,
    firstname character varying,
    lastname character varying,
    companyname character varying,
    email character varying,
    sip_phone integer);

SELECT '(11423, FirstName, LastName, Company, email@gmail.com, 204)' :: t_user_type;

CREATE OR REPLACE FUNCTION change_type(p_user character varying)
  RETURNS t_user_type AS
$BODY$DECLARE
  v_user_type t_user_type;
BEGIN
  v_user_type := CAST(p_user AS t_user_type);
  return v_user_type;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION change_type(character varying)
OWNER TO postgres;

SELECT change_type(
   '(11423, FirstName, LastName, Company, email@gmail.com, 204)');