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?

    RAISE EXCEPTION '% cannot be null', foobar;

This is something that could but doesn't work.

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

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

I'm using PostgreSQL 9.1.

  • is this in a trigger function? In which case, why NEW.entity.colname rather than just NEW.colname? Commented Jul 25, 2012 at 14:04
  • 3
    @JackDouglas: As I understand it, the value of entity.colname is supposed to hold the name of the column. The OP needs dynamic SQL. Commented Jul 25, 2012 at 14:12

2 Answers 2


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);

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

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

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.

  • 1
    Would this be a more readable using EXECUTE 'SELECT $1.' || quote_ident(_name) || ' IS NULL' into a boolean? Commented Jul 25, 2012 at 14:20
  • @JackDouglas: I think that is the most elegant way. I improved my answer with your idea. Commented Jul 25, 2012 at 14:28
  • You can also use format() and the %I placeholder.
    – OrangeDog
    Commented Mar 9, 2022 at 17:46

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:

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 $$
  s text := 'id';
  b boolean;
  execute 'select $1.'||s||' is null' using new into b;
  if b then
    raise exception '% cannot be null', s;
  end if;
  return new;
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

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

  • It indeed is a trigger function, in short i'm looking for a way to address the NEW.name property but "name" is stored in the entity.colname variable. So basically i'm trying to do NEW.{entity.colname} where {entity.colname} should be substituted by the value 'name'
    – ChrisR
    Commented Jul 25, 2012 at 14:19
  • 1
    SELECT INTO seems more elegant, but raises the problem that the type of the column is unknown - as the column name is a variable. You could get around this by casting the value to text and declare n text - as every type can be cast to text. Commented Jul 25, 2012 at 14:23
  • @Erwin good point, thanks. I've (hopefully) achieved the same thing another way. Commented Jul 25, 2012 at 14:33
  • 1
    @Chris the solution is exactly the same whether you are using a simple or compound type, so I've not explicitly used entity.colname - does that make sense to you? Commented Jul 25, 2012 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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