0

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.

MattRgx
  • 35
  • 8
  • 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 Oct 26 '21 at 09:48

1 Answers1

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;
ncank
  • 946
  • 5
  • 15
  • Ok thanks and the query itself, how do i put the parameter ? something like : CALL some procedure ($1, $2, $3) – MattRgx Oct 26 '21 at 09:50
  • 1
    in the query text, put $1, $2, $3... to wherever you need the parameters. updating the answer to cover your question – ncank Oct 26 '21 at 09:53