Want to call a Stored Proc that generates sequence based on arg

Question

I'd like to write a Postgres Stored Proc (cannot be a function) that accepts two args: seq_name and current ID.

If the ID has a value then the SP should return the value. If the ID is NULL then the code should call the sequence name passed in as arg and return that nextval.

I'd then like an example of calling that within PGAdmin. I thin it's do/begin/call but I don't know how to get that returned ID and display it.

Hope that makes sense. Tx

Was thinking it would be something like this:

CREATE OR REPLACE PROCEDURE sp_generate_seq ( s_seq_name VARCHAR(255), i_id INOUT INT )
LANGUAGE plpgsql
AS $$
BEGIN
  CASE id_gen 
    WHEN i_id IS NULL THEN
      SELECT nextval.s_seq_name
    INTO i_id ;
  END id_gen
END $$

do
$$
declare i_val int ;
begin
call sp_generate_seq ( 'foo_seq', i_val);  -- Should return next sequence value since i_val is NULL
raise notice '%', i_val ;
-- I hope that calling again should return the same value since nextval will not be called
call sp_generate_seq ( 'foo_seq', i_val);  
raise notice '%', i_val ;
end
$$

If you want to return a single value, use a function. Stored procedures aren't meant for that. Additionally `nextval()` is a **function**, you can call it using `nextval.some_sequence` https://www.postgresql.org/docs/current/functions-sequence.html — , Oct 25 '22 at 14:43
I'm calling from an external app that only allows Stored Procs to be called - not Functions. — Bill C42, Oct 25 '22 at 15:03

Zegarek · Answer 1 · 2022-10-25T15:51:32.450

You don't need PL/pgSQL, plain SQL will be enough. You can also replace your CASE with coalesce() which returns the first non-null value in the argument list.

CREATE OR REPLACE PROCEDURE sp_generate_seq(s_seq_name text, i_id INOUT bigint)
LANGUAGE sql
AS '
  select coalesce(i_id,nextval(s_seq_name));
';
--CREATE PROCEDURE

If you're not seeing the output of RAISE, you can insert the results of your tests to a table and check it after the anonymous function block.

create table sp_generate_seq_test (attempt serial, output bigint);
--CREATE TABLE
do
$$
declare 
    i_val bigint;
begin
    call sp_generate_seq ( 'test_sequence', i_val);  -- Should return next sequence value since i_val is NULL
    raise notice '%', i_val ;
    insert into sp_generate_seq_test(output) values (i_val);
-- I hope that calling again should return the same value since nextval will not be called
    call sp_generate_seq ( 'test_sequence', i_val);  
    raise notice '%', i_val ;
    insert into sp_generate_seq_test(output) values (i_val);
end
$$;
--NOTICE:  1
--NOTICE:  1
--DO
table sp_generate_seq_test;
-- attempt | output
-----------+--------
--       1 |      1
--       2 |      1
--(2 rows)

Want to call a Stored Proc that generates sequence based on arg

1 Answers1