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;

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

I'm using PostgreSQL 9.1.

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

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.

• Jack Douglas: Would this be a more readable using EXECUTE 'SELECT 1ドル.' || quote_ident(_name) || ' IS NULL' into a boolean?
• Erwin Brandstetter: @JackDouglas: I think that is the most elegant way. I improved my answer with your idea.
• OrangeDog: You can also use format() and the %I placeholder.

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

• ChrisR: 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'
• Erwin Brandstetter: 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.
• Jack Douglas: @Erwin good point, thanks. I've (hopefully) achieved the same thing another way.
• Jack Douglas: @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?