While the acceptance of variables in PostgreSQL “might” be a well-known and simplistic task, to me, the journey to find a method that actually satisfied my need for complete simplicity and diversity eluded me. Yes, I searched the Net and found quite a few, what I’d say, simplistic ways to get variables set within a SQL script. But none of them offered a full spectrum of variety or variability to their use.
For instance, within psql, there is actually a way to accept and set a variable though the PROMPT command:
\prompt [TEXT] NAME prompt user to set internal variable
Unfortunately, I found it quite difficult to use when trying to handle text variables, specifically when trying to wrap single quotes around a text string to be used in a comparison. If you have found out how to do this effectively, please send me an email.
That aside, I still think I’ve stumbled across a better way that allows me to tap into the power of the Unix environment and, in effect, merge two great scripting environments. Enough said, here is a simple script that will prompt for an owner and execute a simple SQL query against the pg_authid catalog and report on an oid and rolname.
\set owner `read -p "Enter owner: " owner && echo \'$owner\'`
SELECT pa.oid,
pa.rolname
FROM pg_authid pa
WHERE pa.rolname = :owner;
\unset owner
This script:
- uses the Unix read command, with -p option to prompt for owner
- executes a second Unix command (&&) in the same line to echo back the owner environment variable
- will set the psql variable owner
- executes the SQL command, using the set :owner variable
- unsets the owner variable
And that is all there is to it. Hopefully you can see the flexibility you have in setting a variable for psql by hooking in the Unix environment. The sky is the limit.
