Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using positional parameter ($1,..) in psql

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]';
like image 561
Krut Avatar asked Aug 01 '14 23:08

Krut


People also ask

What does $1 mean in Postgres?

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.

What is the use of positional parameter?

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.

What is the positional parameter explain with example?

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.

What is a positional parameter in a 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.


2 Answers

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.

Only half a solution

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.

like image 71
Erwin Brandstetter Avatar answered Sep 29 '22 08:09

Erwin Brandstetter


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.

like image 43
Hambone Avatar answered Sep 29 '22 08:09

Hambone