How to execute SELECT with params in DataGrip / PgAdmin etc..?

Question

I need to execute a complex query for testing purposes with params.

How to write a SELECT query which can be executed in DataGrip / PgAdmin with params:

EXECUTE 'SELECT * FROM tenant where id = $1'
   USING 6;
END

(this doesn't work)

and I need to get the same result as from:

SELECT * FROM tenant where id = 6

Thank you

Does it answer your question https://stackoverflow.com/a/54781984/8203759 ? — Konstantin Annikov, Apr 29 '22 at 08:12
Variables can only be used inside e.g. a `DO` block - but a `DO` block can't return a result. — , Apr 29 '22 at 08:13
@KonstantinAnnikov no it does not - I have more than 100 variables which I don't want to enter manually — David, Apr 29 '22 at 08:15
https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query — , Apr 29 '22 at 08:24

score 0 · Answer 1 · answered Apr 29 '22 at 12:58

Finally found solution, even it's a bit tricky:

DO
$$
    DECLARE
        res RECORD;
    BEGIN
        drop table if exists my_temp_table;

        EXECUTE 'CREATE TEMP TABLE my_temp_table AS
        SELECT * FROM tenant where id = $1'
            USING 3243;

    END
$$;
select * from my_temp_table;

How to execute SELECT with params in DataGrip / PgAdmin etc..?

1 Answers1