4 ms·
The situation I'm thinking of (and I think janpot is) is where your stored procedure or function isn't intended to be a shared interface, but a part of what is
by mjw1007 7y ago
The situation I'm thinking of (and I think janpot is) is where your stored procedure or function isn't intended to be a shared interface, but a part of what is notionally your client's code that you happen to want to run server-side.
DO can be good for this, but it doesn't cover all cases: sometimes you want a named function to use in an expression, and sometimes you want multiple procedures which can call each other.
Just having CREATE [OR REPLACE] TEMPORARY FUNCTION would be pleasant.
- jeltz 7y agoYou can create temporary functions in PostgreSQL, but I have not checked the performance of using them (I think they could theoretically bloat the catalog, but I only use them in migrations). Just do something like the below: CREATE FUNCTION pg_temp.f(int, int) RETURNS int LANGUAGE sql AS $$ SELECT $1 + $2 $$; SELECT $1 + $2 $$;SELECT pg_temp.f(1 , 3); f --- 4 (1 row)
- mjw1007 7y agoThis is in fact what I do. I'm agreeing with janpot that a richer model would be pleasant. (In practice, when I've wanted this sort of thing it's been good enough to either install all the functions the client might want to use whenever it makes a connection, or else to use CREATE OR REPLACE FUNCTION every time it's going to run a query needing that function. But I can imagine cases where neither of those would be efficient enough and I'd end up having the client track what it's already loaded. Some help from the server would be good there.)
- janpot 7y agoYep, I just found out about temporary functions today. Gonna start experimenting with that.