5

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?

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.