How to add parameter values to pgadmin sql query?

Question

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.

Brian Burns · Accepted Answer · 2018-05-20 15:26:13Z

41

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.

edited May 20, 2018 at 15:26

Brian Burns

22.4k10 gold badges93 silver badges80 bronze badges

answered Oct 7, 2015 at 15:21

Gabriel's Messanger

3,36822 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chrismarx Over a year ago

sql developer just sends up a dialog prompt and lets you add them, even saves your last values, very handy

faintsignal Over a year ago

@chrismarx Nice feature. Sadly, pgadmin 4 did not introduce this feature.

chrismarx Over a year ago

I switched to DBeaver (dbeaver.com), it supports params like SELECT * FROM table WHERE col1 = :param1 AND col2 = :param2

Thick_propheT Jun 5 at 4:15

I've found that my IDE will barf if I run this solution more than once, so I usually put a DEALLOCATE usrrptplan; below this.

The Coder · Accepted Answer · 2020-02-27 21:53:47Z

14

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.

answered Feb 27, 2020 at 21:53

The Coder

5,3942 gold badges33 silver badges37 bronze badges

2 Comments

vaibhavcool20 Feb 5 at 6:09

how to run this in JDBC?

The Coder Feb 20 at 22:15

You could use NamedParameterJdbcTemplate from the Spring Framework or an Object Relational Mapping (ORM) framework like Hibernate @vaibhavcool20

Collectives™ on Stack Overflow

How to add parameter values to pgadmin sql query?

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related