1

I have a function where I want to be able to use a text variable as a field name in a Postgres function. 

DECLARE
_currentEmployee text;
_myCount integer;

BEGIN

FOR _record IN 
        SELECT ...
LOOP

        _currentEmployee = _record.name
        _myCount = (SELECT count(*) FROM tblTraining
                    WHERE tblTraining._currentEmployee = 4);

        If _mycount = 0 ...

END LOOP;

Basically it's a routine to check the training record of each employee. The table schema has obvious issues because the employees are all entered as columns in the training table instead of normalized id's in each row, but I have to work with what I've got here.

Whenever I run the function, it treats the _currentEmployee literally instead of replacing it with the field name and processing correctly. I get the error:

_currentlEmployee is not a field of tblTraining

Suggestions?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
C Zandstra
  • 47
  • 2
  • 10
  • Please always provide the *complete* function definition, including header and language definition (even if its not currently working). And your version of Postgres. – Erwin Brandstetter Apr 28 '14 at 16:07

2 Answers2

4

Generally, parameterized identifiers are not allowed in SQL.

In a PL/pgSQL function this can be circumvented with a dynamic SQL string executed by EXECUTE. Be wary of SQL injection. Column names have to be treated like user input.

DECLARE
   _rec record;
   _ct  integer;
BEGIN

FOR _rec IN 
   SELECT ...
LOOP
   EXECUTE format(
     'SELECT count(*) FROM tblTraining
      WHERE  tbltraining.%I = 4', _rec.name);  -- %I avoids SQL injection
   INTO _ct;

   IF _ct = 0 THEN ...

END LOOP;

format() with %I sanitizes strings to be valid identifiers. Prevents syntax errors and SQL injection. Be aware that unquoted identifiers are always cast do lower case in Postgres. If your true column names are lower-cased, you have to provide the lower-cased version here.
Never use CaMeL-case identifiers in Postgres to avoid all related problems to begin with.

Try a search for + for many more examples:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you very much for the suggestions! SELECT INTO is the missing piece that I had not considered. I also appreciate the input on avoiding SQL injection. I will do more research into format() with %I so can clearly understand it before using. – C Zandstra Apr 28 '14 at 18:46
1

EXECUTE should be your friend on this task.

http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html

EXECUTE 'SELECT count(*) FROM tblTraining WHERE '
    || _currentEmployee
    || ' = 4'
   INTO _myCount;
Aret
  • 475
  • 3
  • 9