add parameter to dynamic EXECUTE in postgreSQL

Question

I have a procedure which contain this piece of code

SELECT query INTO query FROM temp_table_test WHERE id = whateverId;

EXECUTE query;

My query variable will contain something like

CALL Someprocedure (withSomeParameters)

How can i make the parameter dynamic in the first proc as they can change time to time and i can't make them static in the variable ?

in t-SQL there is something with

sp_executesql

But as I'm very new to postgres I don't know where to start.

You dont need a fuction or procedure; just an ordinary prepared query will do. The syntax for calling it is similar to calling a function. — wildplasser
– wildplasser, Commented Oct 26, 2021 at 9:48

ncank · Accepted Answer · 2021-10-26 09:56:10Z

2

assuming you are using pl/pgsql, you can pass parameters with the USING clause;

EXECUTE query USING $1, $2, $3, ...;

for example;

query = 'SELECT SUM( "field_a" ) FROM "table" WHERE "field_b" = $1 AND "field_c" = $2';
EXECUTE query USING 5, 10;

this will be equal to:

SELECT SUM( "field_a" ) FROM "table" WHERE "field_b" = 5 AND "field_c" = 10;

answered Oct 26, 2021 at 9:39

ncank

9665 silver badges15 bronze badges

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

2 Comments

Ok thanks and the query itself, how do i put the parameter ? something like : CALL some procedure ($1, $2, $3)

in the query text, put $1, $2, $3... to wherever you need the parameters. updating the answer to cover your question