-
Use clause
USING
ofEXECUTE
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;
Postgresql動態sql的使用技巧
最新推荐文章于 2022-09-17 20:46:07 发布