在 postgresql 中使用变量作为字段名

问题描述

我有一个函数，我希望能够在 Postgres 函数中使用文本变量作为字段名称.

DECLARE_currentEmployee 文本；_myCount 整数；开始FOR _记录输入选择 ...环形_currentEmployee = _record.name_myCount = (SELECT count(*) FROM tblTraining哪里 tblTraining._currentEmployee = 4);如果 _mycount = 0 ...结束循环；

检查每个员工的培训记录基本上是例行公事.表架构有明显的问题，因为员工都作为列输入到训练表中，而不是每行中的规范化 ID，但我必须使用这里的内容.

每当我运行该函数时，它都会按字面意思处理 _currentEmployee，而不是将其替换为字段名称并正确处理.我收到错误:

_currentlEmployee 不是 tblTraining 的字段

建议?

解决方案

通常，SQL 中不允许使用参数化标识符.

在 PL/pgSQL 函数中，这可以通过由 EXECUTE.警惕 SQL 注入.列名必须被视为用户输入.

DECLARE_rec 记录；_ct 整数；开始FOR _rec 输入选择 ...环形执行格式('SELECT count(*) FROM tblTrainingWHERE tbltraining.%I = 4', _rec.name);-- %I 避免 SQL 注入INTO _ct;如果 _ct = 0 那么 ...结束循环；

format() 和 %I 将字符串清理为有效标识符.防止语法错误和 SQL 注入.请注意，在 Postgres 中，未加引号的标识符总是被强制转换为小写.如果您的真实列名是小写的，则必须在此处提供小写版本.
切勿在 Postgres 中使用 CaMeL-case 标识符以避免所有相关问题.

尝试搜索 plgsql + dynamic-sql 了解更多示例:

• https://stackoverflow.com/questions/tagged/plpgsql+dynamic-sql

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:

Suggestions?

解决方案

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 plgsql + dynamic-sql for many more examples:

• https://stackoverflow.com/questions/tagged/plpgsql+dynamic-sql

这篇关于在 postgresql 中使用变量作为字段名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！