Dynamic access to record column in plpgsql function

Question

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;

PS: the example above is just for illustration what I'm looking to achieve, don't judge the functionality :)

PS2: I'm using postgresql 9.1

Answer 1

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.

Answer 2

assuming the question is about triggers (and I'm not 100% sure I understand the question), something along these lines may be the way to go:

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create table t(id serial);
--
create function f() returns trigger language plpgsql set search_path to 'stack' as $$
declare
  s text := 'id';
  b boolean;
begin 
  execute 'select $1.'||s||' is null' using new into b;
  if b then
    raise exception '% cannot be null', s;
  end if;
  return new;
end;$$;
--
create trigger trig before insert on t for each row execute procedure f();
--
insert into t default values;
--INSERT 0 1
insert into t(id) values (null);
--ERROR:  id cannot be null
--
rollback;

_{* edited to allow for the case when the column type is unknown (thanks @Erwin!)}