Обсуждение: 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
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

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

> 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;