Dynamic Casting from Text in PL/pgSQL

dynamic-sqlplpgsqlpostgresql

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?

Best Answer

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.

Related Solutions

Postgresql – Postgres plpgsql – Using a variable inside of a dynamic create statement

In addition to what @filiprem wrote, here is how you do this properly:

...
DECLARE
   tbl_var text := 'myTable';   -- I would not use mixed case names ..
BEGIN
EXECUTE '
CREATE TABLE ' || quote_ident(tbl_var) || '( 
   foo integer NOT NULL, 
   bar text NOT NULL)';
...

Use quote_ident() to avoid SQL injection or syntax errors. It will quote names with non-standard characters or reserved words.

I also replaced the double-quotes you had around the string value in your example with single-quotes.

Postgresql – Dynamic access to record column in plpgsql function

That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE.

Here is a demo how to get around this:

CREATE TYPE mytype AS (id int, txt text);

DO
$body$
DECLARE
    _val    mytype  := (1, NULL)::mytype;
    _name   text    := 'txt';
    _isnull boolean;
BEGIN
    EXECUTE 'SELECT $1.' || quote_ident(_name) || ' IS NULL'
    USING _val
    INTO _isnull;

    IF _isnull THEN
        RAISE NOTICE 'Column "%" cannot be null', _name;
    END IF;
END;
$body$

Improved with with @Jack's idea in the comment.

You cannot use plpgsql built-in FOUND because it is not set by EXECUTE (except for RETURN QUERY EXECUTE - more here). That's why I used GET DIAGNOSTICS ... initially. But finally simplified with @Jack's idea.

quote_ident() makes sure the name is syntactically valid and protects against SQLi.

Best Answer

Related Solutions

Postgresql – Postgres plpgsql – Using a variable inside of a dynamic create statement

Postgresql – Dynamic access to record column in plpgsql function

Related Question