Postgresql動態sql的使用技巧

最新推荐文章于 2025-07-20 15:13:38 发布

原创最新推荐文章于 2025-07-20 15:13:38 发布 · 191 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何在PostgreSQL中使用PL/pgSQL语言来执行动态SQL语句，包括使用EXECUTE语句插入数据和使用format函数构建更复杂的查询。通过实例展示了如何安全地构建并执行动态SQL，提高代码的可读性和执行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Use clause USING of EXECUTE statement for usual parameters.
```
DO $$
DECLARE
  tablename text := 'mytab';
  from_date date := CURRENT_DATE;
BEGIN
   EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' VALUES($1)'
      USING from_date;
END
$$;
```
This code will be safe (due using quote_ident function), little bit faster (due using binary value of from_date variable - removed multiple string<->date conversions and little bit more readable (because string expression is shorter).

Use function format. The building query string will be shorter and more readable (table aliases helps too):

query := format('
SELECT value
   FROM %I _dtn
        INNER JOIN temp_table_icrm_prob_flow t ON t.date_valid = _dtn.date_valid
  WHERE _dtn.id_location = $1
    AND _dtn.date_product = $2
    AND _dtd.id_member = $3'
                 , tablename);
 EXECUTE query INTO curr_value USING location_id, product_date, count;