current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
    
Can you also add what version of Postgres you are on? –  Bob Feb 13 at 17:55
    
I'm using 9.3, and pgAdmin 1.18.0. –  anagarD Feb 14 at 9:41

1 Answer 1

up vote 0 down vote

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)');
share|improve this answer
    
I tried casting data like you did, and it works! Thank you! I always do my SELECT INTO like this, and postgres never complains. –  anagarD Feb 14 at 9:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.