Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to add parameter values to pgadmin sql query?

Tags:

In pgadmin3, I would like to use parameterized queries (to faster debugging, just copy & paste the query from my php file). But I haven't found an option to add the values of the $1, $2... parameters. Is it possible?

This is the query I'm building in a loop, following the suggestion for NULL testing from here:

SELECT EXISTS(SELECT 1               FROM tax               WHERE (addby=$1 or addby<>$1)                     AND (adddate=$2 or adddate<>$2)                     AND ($3 IS NULL AND nome IS NULL OR nome=$3)                     AND ($4 IS NULL AND rank IS NULL OR rank=$4)                     AND ($5 IS NULL AND pai IS NULL OR pai=$5)                     AND ($6 IS NULL AND valido IS NULL OR valido=$6)                     AND ($7 IS NULL AND sinonvalid IS NULL OR sinonvalid=$7)                     AND ($8 IS NULL AND espec IS NULL OR espec=$8)                     AND ($9 IS NULL AND public IS NULL OR public=$9)        ); 

Notice that substitute all parameters by hand is tedious, error-prone and probably (I hope) unnecessary.

Thanks in advance.

like image 201
Rodrigo Avatar asked Oct 07 '15 15:10

Rodrigo


People also ask

How do you pass parameters in PostgreSQL query?

The get_sum() function accepts two parameters: a, and b, and returns a numeric. The data types of the two parameters are NUMERIC. By default, the parameter's type of any parameter in PostgreSQL is IN parameter. You can pass the IN parameters to the function but you cannot get them back as a part of the result.

How do you write a select query in pgAdmin?

For example, type “SELECT * FROM” (without quotes, but with a trailing space), and then press the Control+Space key combination to select from a popup menu of autocomplete options. After entering a query, select the Execute/Refresh icon from the toolbar.


2 Answers

I only know two ways.

First is to use PREPARED STATEMENT (Example after PostgreSQL Manual):

PREPARE usrrptplan (int) AS     SELECT * FROM users u, logs l     WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2;  EXECUTE usrrptplan(1, current_date); 

PREPARE creates a prepared statement. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed.

Prepared statements can take parameters: values that are substituted into the statement when it is executed. When creating the prepared statement, refer to parameters by position, using $1, $2, etc.

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again.

Second is to "find-and-replace" $1, $2, .. etc. by proper values. But you want to avoid this one.

like image 180
Gabriel's Messanger Avatar answered Oct 08 '22 04:10

Gabriel's Messanger


In DBeaver you can use parameters in queries just like you can from code, so this will work:

select * from accounts where id = :accountId 

When you run the query DBeaver will ask you for the value for :accountId and run the query.

like image 37
The Coder Avatar answered Oct 08 '22 02:10

The Coder