Geek asks and answers

PostgreSQL - Invalid input syntax for integer

I created a composite type called 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);

... and I need to overlay a string on this type, so I do

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

everyone is great. No mistakes, nothing. But when I do the same with the procedure, I get this error: Invalid input syntax for integer: "(11423," FirstName "," LastName "," Company "," email@gmail.com ", 204)."

Here is my procedure:

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;

And here is a query that uses the procedure:

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

Can anyone tell me what I'm doing wrong?

+3
source share
1 answer

, . , , , . , . SELECT IN , ( , ​​ )? , .

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)');
+1

More articles:


All Articles