I often want to copy/paste sql out of my program code and test/debug in psql and it is tedious to have to replace positional arguments with literal values. Is there a good way to convert:
select * from users where name=$1 and email=$2;
to:
select * from users where name='troy' and email='[email protected]';
Arguments to the SQL function are referenced in the function body using the syntax $n: $1 refers to the first argument, $2 to the second, and so on.
A positional parameter is an argument specified on the command line, used to launch the current process in a shell. Positional parameter values are stored in a special set of variables maintained by the shell.
A positional parameter is a parameter denoted by one or more digits, other than the single digit 0 . Positional parameters are assigned from the shell's arguments when it is invoked, and may be reassigned using the set builtin command.
A positional parameter is a variable within a shell program; its value is set from an argument specified on the command line that invokes the program. Positional parameters are numbered and are referred to with a preceding ``$'': $1, $2, $3, and so on. A shell program may reference up to nine positional parameters.
You could use psql variables. Those are interpolated in SQL code. Per documentation:
A key feature of psql variables is that you can substitute ("interpolate") them into regular SQL statements, as well as the arguments of meta-commands. Furthermore, psql provides facilities for ensuring that variable values used as SQL literals and identifiers are properly quoted. The syntax for interpolating a value without any quoting is to prepend the variable name with a colon (
:
).
Note that (per documentation):
The name must consist of letters (including non-Latin letters), digits, and underscores.
So you cannot work with positional parameters of the form $1
. I am assuming you copy these pieces of code from function bodies, and that's the reason for the positional parameters?
Since PostgreSQL 9.2, even SQL functions can reference parameters by name. Per documentation:
Arguments of a SQL function can be referenced in the function body using either names or numbers.
PL/pgSQL functions have been supporting named parameters in the function body since v8.0.
My preferred naming convention is to prepend function parameters with _
to avoid naming conflicts. But that's a matter of taste and style.
So, your example could work like this:
db=> \set _name 'troy'
db=> \set _email '[email protected]'
db=> select * from users where name=:'_name' and email=:'_email';
You still have to prepare query strings ...
Note the quotes in :'_name'
. That has the same effect as applying quote_literal()
on the string. Details in the manual.
It's not a whole lot better than just running the original query, but assuming that your example is only a trivial one and the actual query is a bit more involved, you could create a function:
CREATE OR REPLACE FUNCTION get_users(user_name varchar, email_addr varchar)
RETURNS SETOF users AS
$BODY$
select *
from users
where
name = user_name and
name = email_addr
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
To get the results you would:
select * from get_users('troy', '[email protected]')
Again with your example, this doesn't help a whole lot, but I've used this with big queries with lots of joins, and it works nicely.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With