Is there a EASY way to use a variable in a PostgreSQL query?

Question

Using: PostgreSQL10 & pgAdmin4

So this query is simple:

SELECT * FROM [schema_name].[table_name] 
   WHERE [schema_name].[table_name].[field_name] = 'search_value';

In Transact-SQL you can create a variable for "search_value":

DECLARE @find varchar(30) = 'search_value';

SELECT * FROM [schema_name].[table_name] 
   WHERE [schema_name].[table_name].[field_name] = @find;

And even re-use the variable:

SET @find = 'new_search_value';

I have now tried everything to mimic this in PostgreSQL, but I can't get it working.

Any suggestions???

Answer 1

You can do this with a custom configuration value:

SELECT set_config('vars.find', 'search_value', false);

SELECT * FROM schema_name.table_name
   WHERE schema_name.table_name.field_name = current_setting('vars.find');

The name needs a namespace (vars in this case), but you can use any random string for this.

That last boolean parameter to set_config() determines whether the new value lasts for the rest of your session (false) or resets at the end of the current transaction (true).