There was a requirement in my office to be able to use variables for table names and field values in PostgreSQL queries.
If you have used Oracle DB, then you know you can define variables using
define and later use those variables in your queries. Here are two variable declarations, one for a table name and another for a field value.
define mytbl = 'sometable'; define maxid = 2500;
Once those variables are defined, we can use them in our queries like this:
select count(*) from &mytbl where id <= &maxid;
This is possible to do in Postgres using the PostgreSQL PgAdmin III client. The feature to use from PgAdmin III is pgScript.
Here is the code to do this. First, you would enter something like this in the SQL Editor:
declare @mytbl, @maxid; set @mytbl = 'sometable'; set @maxid = 2500; set @res = select count(*) from @mytbl where id <= @maxid; print @res;
Instead of just executing this script like usual, you need to click on the icon next to execute query. The icon is “execute pgScript” and it has a P with a GS below it. Once it runs, it will show both the query (substituted with the variable table name and field value) and the results of your query.
So if you ever need to define your variables at the top of a PostgreSQL script and later use them inside your complicated SQLs below, then this is one way to do it.