5

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???

Community
  • 1
  • 1
Pieter Coetzer
  • 75
  • 2
  • 8

1 Answers1

12

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).

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
  • Finally a clean cut response. Shot, @nick-barnes !! I have tried using a With clause, changing the query into a function, declare statements, temp tables, etc. and this makes the most sense (and it might be me coming from an MS SQL background)... – Pieter Coetzer May 30 '19 at 23:36