Struggling to create a "stored procedure" beyond INSERT

Question

Whenever I try to call a stored procedure in PostgreSQL that goes beyond inserting data, it takes forever to run, and it isn't that the query is complicated or the dataset is huge. The dataset is small. I cannot return a table from a stored procedure and I cannot even return 1 row or 1 data point from a stored procedure. It says it is executing the query for a very long time until I finally stop the query from running. It does not give me a reason. I can't let it run for hours. Any ideas on what might be happening? I have included stored procedures that I have tried to call.

Non-working example #1:

CREATE PROCEDURE max_duration(OUT maxD INTERVAL) 
LANGUAGE plpgsql AS $$
DECLARE maxD INTERVAL;
BEGIN
    SELECT max(public.bikeshare3.duration)
    INTO maxD
    FROM public.bikeshare3;
END;
$$ ; 

CALL max_duration(NULL);

Non-working example #2:

CREATE PROCEDURE getDataByRideId2(rideId varchar(16))
LANGUAGE SQL
AS $$
    SELECT rideable_type FROM bikeshare3
    WHERE ride_id = rideId
$$;

CALL getDataByRideId2('x78900');

Working example

The only one that worked when called is an insert procedure:

CREATE OR REPLACE PROCEDURE genre_insert_data(GenreId integer, Name_b character varying)
LANGUAGE SQL
AS $$
    INSERT INTO public.bikeshare3 VALUES (GenreId, Name_b)
$$;

CALL genre_insert_data(1, 'testName');

1) How did you `call` the nonworking examples? 2) If you are wanting to return things use `CREATE FUNCTION`. Functions are better suited to that. For `plpgsql` see [Return from function](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING). — Adrian Klaver, Apr 16 '22 at 21:26
Please *always* disclose your version of Postgres. It matters. — Erwin Brandstetter, Apr 16 '22 at 23:09
For 'Nonworking example1' get rid of `DECLARE maxD INTERVAL;` that is interfering with the argument variable. 'Nonworking example 2:` is not going to work as a `PROCEDURE` can only return something via an `OUT`. If you want to `sql` and return from a query use a `FUNCTION` and see [Return output](https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS) — Adrian Klaver, Apr 16 '22 at 23:10
@AdrianKlaver: Mostly on point. But the 2nd example *can* still be a procedure. (Doesn't mean it should.) I added code to my answer. — Erwin Brandstetter, Apr 16 '22 at 23:48

Erwin Brandstetter · Answer 1 · 2022-04-16T23:51:35.750

`FUNCTION` or `PROCEDURE`?

The term "stored procedure" has been a widespread misnomer for the longest time. That got more confusing since Postgres 11 added CREATE PROCEDURE.

You can create a FUNCTION or a PROCEDURE in Postgres. Typically, you want a FUNCTION. A PROCEDURE mostly only makes sense when you need to COMMIT in the body. See:

How to return a value from a stored procedure (not function)?

Nothing in your question indicates the need for a PROCEDURE. You probably want a FUNCTION.

Question asked

Adrian already pointed out most of what's wrong in his comment.

Your first example could work like this:

CREATE OR REPLACE PROCEDURE max_duration(INOUT _max_d interval = NULL) 
  LANGUAGE plpgsql AS
$proc$
BEGIN
   SELECT max(b.duration) INTO _max_d
   FROM   public.bikeshare3 b;
END
$proc$;

CALL max_duration();

Most importantly, your OUT parameter is visible inside the procedure body. Declaring another instance as variable hides the parameter. You could access the parameter by qualifying with the function name, max_duration.maxD in your original. But that's a measure of last resort. Rather don't introduce duplicate variable names to begin with.

I also did away with error-prone mixed-case identifiers in my answer. See:

Are PostgreSQL column names case-sensitive?

I made the parameter INOUT max_d interval = NULL. By adding a default value, we don't have to pass a value in the call (that's not used anyway). But it must be INOUT instead of OUT for this.

Also, OUT parameters only work for a PROCEDURE since Postgres 14. The release notes:

Stored procedures can now return data via OUT parameters.

While using an OUT parameter, this advise from the manual applies:

Arguments must be supplied for all procedure parameters that lack defaults, including OUT parameters. However, arguments matching OUT parameters are not evaluated, so it's customary to just write NULL for them. (Writing something else for an OUT parameter might cause compatibility problems with future PostgreSQL versions.)

Your second example could work like this:

CREATE OR REPLACE PROCEDURE get_data_by_ride_id2(IN _ride_id text
                                               , INOUT _rideable_type text = NULL)  -- return type?
  LANGUAGE sql AS
$proc$
SELECT b.rideable_type
FROM   public.bikeshare3 b
WHERE  b.ride_id = _ride_id;
$proc$;

CALL get_data_by_ride_id2('x78900');

If the query returns multiple rows, only the first one is returned and the rest is discarded. Don't go there. This only makes sense while ride_id is UNIQUE. Even then, a FUNCTION still seems more suitable ...

Struggling to create a "stored procedure" beyond INSERT

1 Answers1

FUNCTION or PROCEDURE?

Question asked

`FUNCTION` or `PROCEDURE`?