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?