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.

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

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. • Would this be a more readable using EXECUTE 'SELECT$1.' || quote_ident(_name) || ' IS NULL' into a boolean? – Jack Douglas Jul 25 '12 at 14:20
• @JackDouglas: I think that is the most elegant way. I improved my answer with your idea. – Erwin Brandstetter Jul 25 '12 at 14:28

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

• 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 Jul 25 '12 at 14:19
• 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. – Erwin Brandstetter Jul 25 '12 at 14:23
• @Erwin good point, thanks. I've (hopefully) achieved the same thing another way. – Jack Douglas Jul 25 '12 at 14:33
• @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? – Jack Douglas Jul 25 '12 at 14:35