How do I call a procedure in TimescaleDB?

user_15989592 0
Viewed 43

I have a stored procedure that starts like this:

CREATE OR REPLACE PROCEDURE my_procedure(job_id int, config jsonb)
LANGUAGE plpgsql
AS
...

I don't really use the two parameters job_id int, config jsonb, I just have them because for you have to have parameters.

But how do I call the procedure, I've tried CALL my_procedure() but obviously it says that it needs the parameters. How do I call the method without using the parameters. Can I just write anything as long as I don't refer to the parameters within the procedure or how does it work?

timescaledb procedure call parameters postgresql
1 Answers

From PostgreSQL 11.x you can create stored procedures without parameters, like

CREATE TABLE foo(a varchar(64));

CREATE PROCEDURE teeeest()
AS $$
  INSERT INTO foo(a) VALUES('THIS WORKS')
$$ LANGUAGE sql;

CALL teeeest();

SELECT * FROM foo;

This is a Fiddle you can test this at: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/5244

Basically nothing stops you from avoiding the specification of parameters in PostgreSQL.

So, your statement of

I just have them because for you have to have parameters.

does not seem to be technically true, but it is possible that some other rules that I am unaware of and that might be specific to your environment or team prevents you from defining parameterless procedures, then job_id and config become meaningless in your example and you can pass any values to them, because they will be ignored anyway.

But how do I call the procedure, I've tried CALL my_procedure() but obviously it says that it needs the parameters.

Since you defined your procedure with parameters, you need to pass values for them. A rule of thumb is that whenever you define a procedure, you will need to use it accordingly. If the definition is improper, then you can CREATE OR REPLACE the procedure accordingly.

user_4365600
Related