PostgreSQL - Invalid input syntax for integer

Question

I have made composite type named 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 cast string to that type, so I do

SELECT '(11423, FirstName, LastName, Company, [email protected], 204)' :: t_user_type;

everythin is fine. No errors, nothing. But when I do that same thing using procedure, I get this error: Invalid input syntax for integer: "(11423," FirstName"," LastName"," Company"," [email protected]", 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 query that uses the procedure:

SELECT change_type(
   '(11423, FirstName, LastName, Company, [email protected], 204)');

Can anybody tell me what am I doing wrong?

Can you also add what version of Postgres you are on?

Kuberchaun
– Kuberchaun

2014-02-13 17:55:43 +00:00
Commented Feb 13, 2014 at 17:55 — Kuberchaun
– Kuberchaun, Commented Feb 13, 2014 at 17:55
I'm using 9.3, and pgAdmin 1.18.0.

anagarD
– anagarD

2014-02-14 09:41:33 +00:00
Commented Feb 14, 2014 at 9:41 — anagarD
– anagarD, Commented Feb 14, 2014 at 9:41

Kuberchaun · Accepted Answer · 2014-02-13 17:50:06Z

1

Not sure why that fails I made small change where I just cast the value into your local variable and it appears to work. But I don't know why yet. Is it legal to SELECT INTO a variable like you were doing(I think so I can do it with regular text string into a text variable)? I usually do it like my example below when it's just a variable with no source table.

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 protected], 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 protected], 204)');

edited Feb 13, 2014 at 17:50

answered Feb 13, 2014 at 17:44

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

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

1 Comment

anagarD Over a year ago

I tried casting data like you did, and it works! Thank you! I always do my SELECT INTO like this, and postgres never complains.

Collectives™ on Stack Overflow

PostgreSQL - Invalid input syntax for integer

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