0

I have a bunch of funcs (or procs) for simple inserts, ex: insert_user( email );

Most of them return the inserted_id, whether it's for other procs or a driver. And some don't, like inserts to many-many tables with no id.

PG stored procs have out params that seem to be treated the SQL Standard way and not like PG funcs. they're not optional/defaultable, so you end up with a trailing null every time you don't need the id, ex:

call insert_user('test@email', null);

That feels clunky. If I could overload it that'd be awesome, but surprisingly you can't with the out params, even though they're a required part of the signature. I could make a second proc for every insert, like:

call insert_user( 'test@email' );
call insert_user_return_id( 'test@email', out_id);

But then I have double the procs to test and maintain. I could switch to functions, which won't require an extra argument for the out_id, and I can ignore the out_id if I don't want it. But the "insert" funcs have to start with SELECT ex:

select insert_user( 'test@email' );
select insert_user( 'test@email' ) into out_id;
select id from insert_user( 'test@email' );

And something about "select insert" feels uncomfortable. Especially with many-many inserts that return void. Any thoughts or advice would be greatly appreciated!

Sean
  • 509
  • 2
  • 11
  • 1
    Sean, why don't you use plain INSERT INTO ... RETURNING statement ? If selecting a function seems uncomfortable, plain inserts seems like a way to go. – Julius Tuskenis Dec 29 '21 at 10:50
  • Thanks for the feedback @JuliusTuskenis ! I get what you're saying: don't use funcs at all. I didn't even think of that. I've always just liked the abstraction and clarity of funcs, mapping driver's to one entry point, then handling auditing/security at that entry point, and adding conditional exception handling like a bool for deciding to throw on "not found" I was also under the impression exec plans were cached in funcs making them perform better. – Sean Dec 29 '21 at 16:20
  • 1
    I like functions too (and use them a lot). So don't feel uncomfortable just because of SELECT statement. Just tell your self you are executing the function and selecting the result it returns. :) You can also name your functions to reflect what they are doing like "user_edit", "user_find". – Julius Tuskenis Dec 30 '21 at 07:34
  • Sound good you sold me, I'll handle it with functions. Thanks @JuliusTuskenis ! – Sean Dec 30 '21 at 20:16

0 Answers0