UUID can be written to row when passed as parameter/argument to a PL/pgSQL function. Feature or bug?
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?
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
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
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
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
Show quoted text
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;