Postgresql – Dynamic access to record column in plpgsql function

composite-typesdynamic-sqlplpgsqlpostgresql

How can I address a column from a record in a plpgsql function dynamically?

In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the IF constrol structure. I'm looking into how I can replace the foobar part in the snippet below with entity.colname. Is that possible in plpgsql?

IF NEW.foobar IS NULL THEN
    RAISE EXCEPTION '% cannot be null', foobar;
END IF;

This is something that could but doesn't work.

IF NEW.entity.colname IS NULL THEN
    RAISE EXCEPTION '% cannot be null', entity.colname;
END IF;

The example above is just for illustration what I'm looking to achieve, don't judge the functionality. 🙂

I'm using PostgreSQL 9.1.

Best Answer

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.

Related Question