[SOLVED] PostgreSQL SET config from FUNCTION args

PostgreSQL SET config from FUNCTION args

Is it possible to use an arg from a SQL FUNCTION to SET a config value?

I'm trying to do something like this, but it doesn't work on PostgreSQL 10.12 (the value of the config must be a number, not an arg):

CREATE OR REPLACE FUNCTION test(query text, threshold real)
RETURNS TABLE(
    t text,
    score real
  )
AS
'SELECT t, word_similarity(query, t) as score
 FROM test_trgm
 WHERE query <% t 
 ORDER BY score DESC'
language 'sql'
SET pg_trgm.word_similarity_threshold TO threshold;

The purpose of doing this is to boost the performance of the function instead of using something like:

WHERE query <% t AND word_similarity(query, t) > threshold

Thanks in advance!

Solution

You can use SET_CONFIG() within the function

CREATE OR REPLACE FUNCTION testsim(query text, threshold real)
RETURNS TABLE(
    t text,
    score real
  )
AS
$$

select set_config('pg_trgm.word_similarity_threshold', threshold::text, true);

SELECT t, word_similarity(query, t) as score
 FROM test_trgm
 WHERE query <% t 
 ORDER BY score DESC
 $$
language 'sql'
;