0

Is there any mistake in my SQL code? The procedure is apparently created, there's no error, but when I call it with CALL user_orders(10) I get no rows. The query itself is valid: if I execute it on its own (not as a procedure), I get the rows I expect. It's PostgreSQL

In summary, this outputs no rows:

CREATE or replace PROCEDURE user_orders(lim INTEGER)
BEGIN ATOMIC
SELECT u.name, o.product, o.price,
o.quantity, (o.quantity * o.price) AS total 
FROM users AS u
JOIN orders AS o
ON u.id = o.user_id
LIMIT lim;
end;
CALL user_orders(10);

But this does output rows:

SELECT u.name, o.product, o.price,
o.quantity, (o.quantity * o.price) AS total 
FROM users AS u
JOIN orders AS o
ON u.id = o.user_id
LIMIT 10;

I tried this as well:

CREATE OR REPLACE PROCEDURE user_orders(lim INTEGER)
LANGUAGE SQL
AS $$
SELECT u.name, o.product, o.price,
o.quantity, (o.quantity * o.price) AS total 
FROM users AS u
JOIN orders AS o
ON u.id = o.user_id
LIMIT lim;
$$;

I believe my code is in line with the documentation

What's wrong?

db-fiddle

1 Answer 1

2

It is in the nature of a procedure not to return results. If you want results, write a function:

CREATE FUNCTION user_orders(lim INTEGER)
   RETURNS TABLE (name text, product text, price numeric, quantity integer, total numeric)
RETURN u.name, o.product, o.price,
o.quantity, (o.quantity * o.price) AS total 
FROM users AS u
...;

I guessed the result data types; use the correct ones.

8
  • Thank you! But if a procedure is a SELECT, shouldn't it perform a SELECT (which involves providing some output) Nov 7, 2023 at 7:19
  • It does perform a SELECT, but that result is lost. How should it pass the result to the caller? Nov 7, 2023 at 8:07
  • @LaurenzAlbe In fairness, this does work in most other DBMSs. Nov 7, 2023 at 12:09
  • @Charlieface Not in Oracle, for one. Perhaps in Microsoft SQL Server (I don't know), but I wouldn't consider that "most DBMSs". Perhaps there is also a confusion that arises because many people use the word "stored procedure" when they talk about a function. Nov 7, 2023 at 12:15
  • SQL Server, MySQL, MariaDB, DB2 all can do this to my knowledge. Maybe not most, certainly some. Nov 7, 2023 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.