postgresql PL/pgSQL -将动态列名传递给PREPARE

im9ewurl 于 2022-11-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(215)

Postgres版本：14
我有一个脚本，它可以执行一些小型的重复性动态查询。这是可行的，但速度很慢，所以我将它们转换为预处理语句。这也是可行的，直到我尝试将列名作为参数传递时，一切似乎都是语法或“operator does not exist”错误。什么是正确的语法来使它像https://dev.to/aws-heroes/postgresql-prepared-statements-in-pl-pgsql-jl3所示的那样工作？

DO
$$
BEGIN
    DECLARE rtmp1 record;   
    DECLARE rtmp2 record;   
    DECLARE rtmp3 record;   
    DECLARE col_name1 text := 'my_field1';
    DECLARE col_name2 text := 'my_field2';
    DECLARE col_name3 text := 'my_field3';

    -- PREPARE QUERIES
    DEALLOCATE ALL;
    EXECUTE FORMAT('PREPARE q_test(text) AS
        SELECT
            first_name
        FROM my_table
        WHERE $1 = 0');

    EXECUTE FORMAT('EXECUTE q_test(%s)', col_name1) INTO rtmp1;
    EXECUTE FORMAT('EXECUTE q_test(%s)', col_name2) INTO rtmp2;
    EXECUTE FORMAT('EXECUTE q_test(%s)', col_name3) INTO rtmp3;
END
$$;

postgresql

来源：https://stackoverflow.com/questions/74234198/pl-pgsql-pass-dynamic-column-name-to-prepare

1条答案

按热度按时间

vlurs2pr1#

我可以让函数运行：

\d my_table 
                           Table "public.my_table"
    Column    |            Type             | Collation | Nullable | Default 
--------------+-----------------------------+-----------+----------+---------
 other_column | character varying(100)      |           |          | 
 updated_at   | timestamp without time zone |           |          | 
 new_colum    | character varying(100)      |           |          | 
 first_name   | character varying           |           |          | 
 id           | integer                     |    

DO          
$$
BEGIN

    -- PREPARE QUERIES
    DEALLOCATE ALL;
    EXECUTE FORMAT('PREPARE q_test(text) AS
        SELECT
            first_name
        FROM my_table
        WHERE %I = 0', 'id');
END
$$;

更新。

函数的一个版本，它遍历字段名并对每个字段名执行查询。取消了PREPARE/EXECUTE。

DO
$$
DECLARE
   fld_name text;
BEGIN

    FOREACH fld_name IN ARRAY array['id', 'first_name'] LOOP
        RAISE NOTICE '%', fld_name;
        EXECUTE FORMAT('SELECT
            first_name
        FROM my_table
        WHERE %I IS NOT NULL', fld_name);
    END LOOP;
END
$$;

NOTICE:  id
NOTICE:  first_name
DO

赞(0）回复(0）举报 2022-11-04

我来回答

postgresql PL/pgSQL -将动态列名传递给PREPARE

1条答案

相关问题

热门标签

最新问答