CODEWITHSUNDEEP
functionpostgresqlplpgsqldynamic-sql
C Zandstra
C Zandstra

Reputation: 47

Using variable for fieldname in postgresql

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?

Upvotes: 1

Views: 4061

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

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:

Upvotes: 4

Aret
Aret

Reputation: 475

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;

Upvotes: 1

Related Questions