Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
1  
@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

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
1  
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!)

share|improve this answer
    
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
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. –  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
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? –  Jack Douglas Jul 25 '12 at 14:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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