Dynamic casting from text within plpgsql

Question

Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query of the form:

UPDATE table SET attribute = x WHERE table_id = y

This is so common that I have a stored procedure that performs this task:

CREATE OR REPLACE FUNCTION setvalue(
 relname text,
 row_id integer,
 colname text,
 newvalue text)
RETURNS void AS
$BODY$
BEGIN
 EXECUTE format('UPDATE %I SET %I = 1ドル WHERE %I = 2ドル', relname, colname, relname || '_id', colname) USING row_id;
END;
$BODY$
LANGUAGE plpgsql;

Unfortunately this doesn't work for non-text types. For instance, updating a date column gives ERROR: column ... is of type date but expression is of type text. Assuming the text is a valid literal representation of the intended type, is there a safe way to get the DBMS to figure out the right type and do the right thing?

Answer 1

My own solution so far is to paste the string literal into the query:

EXECUTE format(
 'UPDATE %I SET %I = ' || quote_literal(newvalue) || ' WHERE %I = 1ドル ',
 relname, colname, relname || '_id') USING row_id;

or just

EXECUTE format(
 'UPDATE %I SET %I = %L WHERE %I = 1ドル', 
 relname, colname, newvalue, relname || '_id') USING row_id;

This works for, e.g., appropriately formatted date types ('1990-05-04'). Probably this sacrifices being able to re-use query plan.