PostgreSQL中如何在子查询前修改GUC参数pg_trgm.similarity_threshold

Perfect scenario for leveraging PostgreSQL's transaction-scoped configuration settings! Here's how you can apply different pg_trgm.similarity_threshold values to each subquery in your UNION without affecting one another:

Method 1: Embed set_config() Directly in Subqueries #

This is the most concise approach, using PostgreSQL's set_config() function to set the threshold only for the duration of the current transaction (each subquery runs with its own configured value):

-- Explicitly list your target columns instead of using * to avoid the temp config column
SELECT col1, col2, first_name, last_name -- Add all your actual columns here
FROM (
  -- First subquery: Threshold for first_name matches
  SELECT 
    set_config('pg_trgm.similarity_threshold', '0.7', TRUE) AS temp_config,
    col1, col2, first_name, last_name
  FROM A 
  WHERE first_name % 'fakeFirstName'
  
  UNION
  
  -- Second subquery: Different threshold for last_name matches
  SELECT 
    set_config('pg_trgm.similarity_threshold', '0.5', TRUE) AS temp_config,
    col1, col2, first_name, last_name
  FROM B 
  WHERE last_name % 'fakeLastName'
) AS result;

Key Details: #

• The third parameter TRUE in set_config() tells PostgreSQL to apply this setting only to the current transaction, so it won't leak into other queries.
• We add a temporary temp_config column to hold the return value of set_config() (it returns the new value as text), then exclude it in the outer query by listing your actual columns instead of using *.
• Since A and B are views of the same table with identical schemas, the UNION will work seamlessly.

Method 2: Use Temporary Tables with SET LOCAL #

If you prefer a more explicit approach (great for complex queries), you can use a transaction with SET LOCAL to configure each subquery, then combine results from temporary tables:

BEGIN;
  -- Configure threshold for first subquery and store results
  SET LOCAL pg_trgm.similarity_threshold = 0.7;
  CREATE TEMP TABLE temp_matches_a AS 
    SELECT * FROM A WHERE first_name % 'fakeFirstName';
  
  -- Reconfigure threshold for second subquery and store results
  SET LOCAL pg_trgm.similarity_threshold = 0.5;
  CREATE TEMP TABLE temp_matches_b AS 
    SELECT * FROM B WHERE last_name % 'fakeLastName';
  
  -- Combine and return the final results
  SELECT * FROM temp_matches_a UNION SELECT * FROM temp_matches_b;
  
  -- Clean up temporary tables (optional, they'll drop when the session ends)
  DROP TABLE temp_matches_a, temp_matches_b;
COMMIT;

Why This Works: #

• SET LOCAL applies the configuration only to the current transaction, so each temporary table is populated with the correct threshold.
• This method is useful if you need to reuse the subquery results multiple times within the same transaction.

Important Notes #

• Avoid using SET without LOCAL or set_config(..., FALSE) — those will change the session-level threshold, which will affect all subsequent queries in your session.
• Ensure you're running these queries in a transaction context (PostgreSQL uses auto-commit by default, but the entire UNION query runs as a single transaction, so Method 1 works reliably).

内容的提问来源于stack exchange，提问作者Olivier Nappert