PostgreSQL动态SQL查询执行

Question

我有一个函数，根据一些 If Else 条件动态生成 SELECT 查询语句。我将该查询语句存储在一个 TEXT 类型的变量中，如下：

    CREATE OR REPLACE FUNCTION func_updateanswercodes(ans_id_param BIGINT, 
                              que_id_param BIGINT, 
                              overwrite_param INTEGER, 
                              new_ans_code_param CHARACTER VARYING)
    RETURNS INTEGER AS
    $BODY$
...................
................
...................
        dyn_sql = 'SELECT ' || que_col_name || ' INTO old_ans_col_val FROM';
                    IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN
                        dyn_sql = dyn_sql || ' profile_answers JOIN registrations ON (pro_ans_frn_pro_id = reg_frn_pro_id)';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'reg_ans' THEN
                        dyn_sql = dyn_sql || ' reg_answers ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'tvl_ans' THEN
                        dyn_sql = dyn_sql || ' tvl_answers '; --35
                                END IF;
                    dyn_sql = dyn_sql || ' WHERE';
                    IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN
                        dyn_sql = dyn_sql || ' reg_id ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'reg_ans' THEN
                        dyn_sql = dyn_sql || ' reg_ans_frn_reg_id ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'tvl_ans' THEN
                        dyn_sql = dyn_sql || ' tvl_ans_frn_reg_id ';
                                END IF;
                        dyn_sql = dyn_sql || '= ' || CAST(temp_reg AS VARCHAR) ||';'

    /* Here want to execute that query in variable dync_sql
...........................
.............................
.......................
    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE

但是使用plpgsql（Postgres的PL / SQL），我不知道如何在变量中执行相同的查询。请帮助我解决这个问题。 提前致谢。

- Satish Sharma

Answer 1

来自文档（Postgres 9.1）：

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

where command-string is an expression yielding a string (of type text) containing the command to be executed. The optional target is a record variable, a row variable, or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored.

换句话说： 将 INTO old_ans_col_val 从字符串移到你执行它的位置。

- Daniel

---

谢谢@Daniel，你能指导我如何在Update查询中实现相同的操作吗？我有一个在dyn_sql中的Update查询，并想要设置一个列值，我该如何使用dyn_sql变量中的动态Update查询呢？ - Satish Sharma

Answer 2

使用EXECUTE，有关详细信息请参见手册

请注意，您必须将INTO部分从查询字符串中取出并放在末尾。

EXECUTE dyn_sql INTO old_ans_col_val;

- Eelke