Passing a UUID Value to a Function in PL/pgSQL

When writing a PL/pgSQL function in Postgres 9.0.x, I ran into a Catch-22 with UUIDs. I tried to write this function to log changes to rows in any table in this "history_" table:

CREATE OR REPLACE FUNCTION make_history_( varchar, varchar, varchar, varchar, varchar, varchar, text ) RETURNS boolean AS $$

BEGIN
  INSERT INTO history_ ( app_user_name_, table_name_, column_name_, affected_uuid_, old_value_, new_value_, notes_, triggered_ )
  VALUES ( $1, $2, $3, $4, $5, $6, $7, True );
  RETURN True;
END;

LANGUAGE plpgsql;

The problem is that our programming uses long hex strings as a representation of the actual 128-bit value that is a UUID. We have no way to actually represent the 128 bits in our source code. So Postgres is generally very kind about accepting a hex string in place of an actual 128-bit value. If we pass Postgres a hex string, Postgres transforms the string into an actual 128-bit value, and then stores those 128 bits efficiently in the database. For SELECT statements, vice-versa, generating a hex string sent as text to the client. This works so well in Postgres that we programmers begin to think of the hex string as the actual UUID – but it is not.

Where this UUID<-->HexString translation broke down for me was passing a hex string into the function above. Notice the 4th argument. The hex string is passed in as varchar, but then we turn around and save it to a table where the column "affected_uuid" is actually of type "uuid". The PL/pgSQL interpreter is a little overeager, and reports a data type mismatch error. It notices that we are passing a varchar, but saving to a column of a different type. I suppose this is a bug in PL/pgSQL, since we can do this with plain SQL in Postgres.

Whether bug or feature, I found a workaround. Declare a variable in your PL/pgSQL code, make it of type "uuid". Assign the argument's value to that variable. Lastly, use that variable in the INSERT command rather than the $x argument itself.


CREATE OR REPLACE FUNCTION make_history_( varchar, varchar, varchar, varchar, varchar, varchar, text ) RETURNS boolean AS $$
DECLARE
  uuid_arg uuid;
BEGIN
  uuid_arg := $4;
  INSERT INTO history_ ( app_user_name_, table_name_, column_name_, affected_uuid_, old_value_, new_value_, notes_, triggered_ )
  VALUES ( $1, $2, $3, uuid_arg, $5, $6, $7, True );
  RETURN True;
END;

LANGUAGE plpgsql;

Update:
Folks on the Postgres General mailing list taught me another workaround -- casting. The standard CAST command or Postgres' own double colon syntax can be used.

CAST( $4 AS uuid )

$4::uuid

So we can write that VALUES line this way:
  VALUES ( $1, $2, $3, CAST( $4 AS uuid ), $5, $6, $7, True );