Thread: UUID can be written to row when passed as parameter/argument to a PL/pgSQL function. Feature or bug?
UUID can be written to row when passed as parameter/argument to a PL/pgSQL function. Feature or bug?
From
Basil Bourque
Date:
If I pass the hex string representation of a UUID to a PL/pgSQL function as a varchar, that value cannot be used directlywhen writing to a row whose column data type is "uuid", in Postgres 9.0.x. Normally Postgres automatically convertsa hex string to a 128-bit UUID value and back again. Is not doing so in a function a bug? Example follows below. (1) Create a simple table with one column of type "uuid". ---> CREATE TABLE uuid_tbl_ ( uuid_col_ uuid NOT NULL ) WITH ( OIDS=FALSE ); <--- (2) Create this function. ---> CREATE OR REPLACE FUNCTION uuid_write_( varchar ) RETURNS boolean AS $$ BEGIN INSERT INTO uuid_tbl_ ( uuid_col_ ) VALUES ( $1 ); RETURN True; END; $$ LANGUAGE plpgsql; <---- (3) Call this function: select uuid_write_('34A94C40-453A-4A30-9404-128121E76570'); Note the error: ----> ERROR: column "uuid_col_" is of type uuid but expression is of type character varying LINE 2: VALUES ( $1 ) ^ HINT: You will need to rewrite or cast the expression. QUERY: INSERT INTO uuid_tbl_ ( uuid_col_ ) VALUES ( $1 ) CONTEXT: PL/pgSQL function "uuid_write_" line 3 at SQL statement ********** Error ********** ERROR: column "uuid_col_" is of type uuid but expression is of type character varying SQL state: 42804 Hint: You will need to rewrite or cast the expression. Context: PL/pgSQL function "uuid_write_" line 3 at SQL statement <------ (4) Change the function by assigning the passed hex string to a variable named 'uuid_arg' and declared to be of type "uuid",then write that variable to the row instead of the argument. -----> CREATE OR REPLACE FUNCTION uuid_write_( varchar ) RETURNS boolean AS $$ DECLARE uuid_arg uuid; BEGIN uuid_arg := $1; INSERT INTO uuid_tbl_ ( uuid_col_ ) VALUES ( uuid_arg ); RETURN True; END; $$ LANGUAGE plpgsql; <----- (5) Run the same line calling this function: select uuid_write_('34A94C40-453A-4A30-9404-128121E76570'); Note the success of this workaround. My blog post on this issue: http://crafted-software.blogspot.com/2011/04/passing-uuid-value-to-function-in.html --Basil Bourque
Re: UUID can be written to row when passed as parameter/argument to a PL/pgSQL function. Feature or bug?
From
Basil Bourque
Date:
Whoops… Typo in the Subject line. Should have been "UUID cannot" rather than "UUID can". UUID cannot be written to row when passed as parameter/argument to a PL/pgSQL function. Feature or bug? ^^^ --Basil Bourque
Re: UUID can be written to row when passed as parameter/argument to a PL/pgSQL function. Feature or bug?
From
Adrian Klaver
Date:
On Thursday, April 14, 2011 6:43:21 pm Basil Bourque wrote: > If I pass the hex string representation of a UUID to a PL/pgSQL function as > a varchar, that value cannot be used directly when writing to a row whose > column data type is "uuid", in Postgres 9.0.x. Normally Postgres > automatically converts a hex string to a 128-bit UUID value and back > again. > > Is not doing so in a function a bug? How about: CREATE OR REPLACE FUNCTION public.uuid_write_(character varying) RETURNS boolean LANGUAGE plpgsql AS $function$ BEGIN INSERT INTO uuid_tbl_ ( uuid_col_ ) VALUES ( $1::uuid ); RETURN True; END; $function$ test(5432)aklaver=>select uuid_write_('34A94C40-453A-4A30-9404-128121E76570'); uuid_write_ ------------- t (1 row) > > Note the success of this workaround. > > My blog post on this issue: > http://crafted-software.blogspot.com/2011/04/passing-uuid-value-to-function > -in.html > > --Basil Bourque -- Adrian Klaver adrian.klaver@gmail.com
Re: UUID can be written to row when passed as parameter/argument to a PL/pgSQL function. Feature or bug?
From
Basil Bourque
Date:
Thanks for the suggestion of casting the hex string to uuid. That works. I tried the standard syntax using "CAST": VALUES ( CAST( $1 AS uuid) ) --Basil Bourque > How about: > CREATE OR REPLACE FUNCTION public.uuid_write_(character varying) > RETURNS boolean > LANGUAGE plpgsql > AS $function$ > > BEGIN > INSERT INTO uuid_tbl_ ( uuid_col_ ) > VALUES ( $1::uuid ); > RETURN True; > END;