Thread: plpgsql functions organisation
AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to
call a function from within a function.
That being said, I would seriously look at how and why you are writing your functions
as functions that call other functions are not very efficient.
Also note that PostgreSQL allows you define functions using Python, so that might be a possible compromise.call a function from within a function.
That being said, I would seriously look at how and why you are writing your functions
as functions that call other functions are not very efficient.
On Sat, May 2, 2015 at 12:53 PM, Yves Dorfsman <yves@zioup.com> wrote:
I find my plpgsql functions becomes unreadable very quickly. I want to break
them up in smaller functions.
What is the best way to organised them?
Is there any way to define functions inside functions?
When I list functions in psql, I can see them all at the same level, is there
any way to organise them in packages like with python and other languages, so
the smaller functions are hidden away in a package/directory?
Thanks.
--
http://yves.zioup.com
gpg: 4096R/32B0F416
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Further to the point of saying functions are ineffiencent, consider the fact that as of the current version of PostgreSQL, plpgsql
functions cannot be pre-optimized. So when they are referenced in a SQL statement, PostgreSQL (optimizer) has load theOn Sat, May 2, 2015 at 5:17 PM, Bill Moran <wmoran@potentialtech.com> wrote:
Maybe that's a roundabout way of saying that if your functions areOn Sat, 2 May 2015 14:07:31 -0700
Jeff Janes <jeff.janes@gmail.com> wrote:
> On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
> > On 05/02/2015 10:12 AM, Melvin Davidson wrote:
> >
> >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible
> >> to
> >> call a function from within a function.
> >>
> >> That being said, I would seriously look at how and why you are writing
> >> your functions
> >> as functions that call other functions are not very efficient.
> >>
> >
> > I am not following. That is what packaging is about, separating out 'units
> > of work' so they can be combined as needed. Part of that is using existing
> > functions in new functions/classes. In fact in the Postgres source I see
> > this in many places. Now it is entirely possible I missed a memo, so I am
> > open to a more detailed explanation of the inefficiencies involved.
> >
>
> The Postgres source is written in C, not in plpgsql. C has a good
> optimizing compiler and plpgsql doesn't.
complex enough to require calling "sub-functions" they might be
justifying being writting in C?
--
Bill Moran
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
OK, Here is a simple example that shows the difference between using a self contained function and
one that calls sub functions.
After loading all the functions below, repeat each of the EXPLAIN statements a few times and note that
callsubs takes almost TWICE as long to execute as nosub.
CREATE OR REPLACE FUNCTION nosub(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str ALIAS FOR $1;
BEGIN
IF LENGTH(p_in_str) <= 6
THEN RAISE NOTICE 'Hi %', p_in_str;
ELSE
RAISE NOTICE 'Hello %', p_in_str;
END IF;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION nosub(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION called1(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str1 ALIAS FOR $1;
BEGIN
RAISE NOTICE 'Hi %', p_in_str1;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION called1(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION called2(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str2 ALIAS FOR $1;
BEGIN
RAISE NOTICE 'Hello %', p_in_str2;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION called2(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION callsubs(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str ALIAS FOR $1;
BEGIN
IF LENGTH(p_in_str) <= 6
THEN PERFORM CALLED1(p_in_str);
ELSE
PERFORM CALLED2(p_in_str);
END IF;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION callsubs(text)
OWNER TO postgres;
EXPLAIN ANALYZE SELECT nosub('melvin');
EXPLAIN ANALYZE SELECT callsubs('melvin');
one that calls sub functions.
After loading all the functions below, repeat each of the EXPLAIN statements a few times and note that
callsubs takes almost TWICE as long to execute as nosub.
CREATE OR REPLACE FUNCTION nosub(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str ALIAS FOR $1;
BEGIN
IF LENGTH(p_in_str) <= 6
THEN RAISE NOTICE 'Hi %', p_in_str;
ELSE
RAISE NOTICE 'Hello %', p_in_str;
END IF;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION nosub(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION called1(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str1 ALIAS FOR $1;
BEGIN
RAISE NOTICE 'Hi %', p_in_str1;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION called1(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION called2(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str2 ALIAS FOR $1;
BEGIN
RAISE NOTICE 'Hello %', p_in_str2;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION called2(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION callsubs(text)
RETURNS void AS
$BODY$
DECLARE
p_in_str ALIAS FOR $1;
BEGIN
IF LENGTH(p_in_str) <= 6
THEN PERFORM CALLED1(p_in_str);
ELSE
PERFORM CALLED2(p_in_str);
END IF;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION callsubs(text)
OWNER TO postgres;
EXPLAIN ANALYZE SELECT nosub('melvin');
EXPLAIN ANALYZE SELECT callsubs('melvin');
On Sat, May 2, 2015 at 7:37 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 05/02/2015 03:28 PM, Bill Moran wrote:On Sat, 02 May 2015 15:06:24 -0700
Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 05/02/2015 02:07 PM, Jeff Janes wrote:On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver <adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>> wrote:
On 05/02/2015 10:12 AM, Melvin Davidson wrote:
AFAIK, you cannot "package" functions in PostgreSQL, but it is
possible to
call a function from within a function.
That being said, I would seriously look at how and why you are
writing
your functions
as functions that call other functions are not very efficient.
I am not following. That is what packaging is about, separating out
'units of work' so they can be combined as needed. Part of that is
using existing functions in new functions/classes. In fact in the
Postgres source I see this in many places. Now it is entirely
possible I missed a memo, so I am open to a more detailed
explanation of the inefficiencies involved.
The Postgres source is written in C, not in plpgsql. C has a good
optimizing compiler and plpgsql doesn't.
Does this actually matter? I am a biologist that backed into computing,
so I realize I am weak on the fundamentals. Still the scientist in me
wants data backing assertions. As I understand it plpgsql works close to
the server and is optimized to do so. I know writing in C would be a
better solution. Still is calling plpgsql functions inside plpgsql
really a bad thing when just considering plpgsql?
The answer to that is the same answer to so many other things: it depends.
plpgsql functions are slower than C. They also lack a lot of language
features that C has. That being said, if they're meeting your needs, then
don't worry about it. plpgsql is around because for most people, it works
well enough. There are certainly cases when you want to create very complex
logic in the database and plpgsql is liable to make that difficult. But
there are a lot of cases where having to manage pointers and a build
environment and all the things that go with C aren't justified, because
plpgsql has none of that complexity. There are advantages both ways.
The beauty of PostgreSQL is that you have both available and you
can choose whichever is best for your situation.
Agreed, though in my case I drop into plpythonu when I want more complex solutions.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
The point was to show that yes, function calls take time, and using sub functions take even more time. I am not about to write an additional more detailed example just to show the same results. If you are in doubt, I respectfully suggest you do your own testing.
On Sun, May 3, 2015 at 5:26 AM, Alban Hertroys <haramrae@gmail.com> wrote:
> On 03 May 2015, at 2:56, Melvin Davidson <melvin6925@gmail.com> wrote:
>
> OK, Here is a simple example that shows the difference between using a self contained function and
> one that calls sub functions.
>
> After loading all the functions below, repeat each of the EXPLAIN statements a few times and note that
> callsubs takes almost TWICE as long to execute as nosub.
>
> CREATE OR REPLACE FUNCTION nosub(text)
> RETURNS void AS
> $BODY$
...
> IF LENGTH(p_in_str) <= 6
> THEN RAISE NOTICE 'Hi %', p_in_str;
> ELSE
> RAISE NOTICE 'Hello %', p_in_str;
> END IF;
>
> RETURN;
> END;
> $BODY$
…
> CREATE OR REPLACE FUNCTION called1(text)
> RETURNS void AS
> $BODY$
...
> RAISE NOTICE 'Hi %', p_in_str1;
>
> RETURN;
> END;
> $BODY$
…
> CREATE OR REPLACE FUNCTION called2(text)
> RETURNS void AS
> $BODY$
...
> RAISE NOTICE 'Hello %', p_in_str2;
>
> RETURN;
> END;
...
That's a rather uninteresting experiment, as all it does is call a function and raise a notice. Relative to what the functions do, the function call itself takes a significant amount of time. No surprise there, you'll see something similar in any language, even C. All you're showing is that calling a function takes some amount of time > 0.
In C, a function call needs to look up an address to jump to, in plpgsql the database needs to look up the function body in a table. If the function is small and atomic it often gets called from multiple other functions and is probably cached anyway. The main difference between C and plpgsql here is that the latter is an interpreted language, so it does need to read in the entire function body after a call - which I'd expect to be quite a bit faster with a smaller (atomic) function body, especially when it hasn't been cached yet.
So far I haven't been convinced.
An actual use-case where the functions actually do something would be far more interesting. I doubt anybody writes functions just to raise a notice. I expect that in reality most plpgsql functions perform database queries and do something with the result. In such cases, function call overhead could be significant if the call is done for each record in a result set, for example. And even then it's worth considering whether that matters to your situation enough that it outweighs the usual benefits of code separation.Alban Hertroys
> On Sat, May 2, 2015 at 7:37 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 05/02/2015 03:28 PM, Bill Moran wrote:
> On Sat, 02 May 2015 15:06:24 -0700
> Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 05/02/2015 02:07 PM, Jeff Janes wrote:
> On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 05/02/2015 10:12 AM, Melvin Davidson wrote:
>
> AFAIK, you cannot "package" functions in PostgreSQL, but it is
> possible to
> call a function from within a function.
>
> That being said, I would seriously look at how and why you are
> writing
> your functions
> as functions that call other functions are not very efficient.
>
>
> I am not following. That is what packaging is about, separating out
> 'units of work' so they can be combined as needed. Part of that is
> using existing functions in new functions/classes. In fact in the
> Postgres source I see this in many places. Now it is entirely
> possible I missed a memo, so I am open to a more detailed
> explanation of the inefficiencies involved.
>
>
> The Postgres source is written in C, not in plpgsql. C has a good
> optimizing compiler and plpgsql doesn't.
>
> Does this actually matter? I am a biologist that backed into computing,
> so I realize I am weak on the fundamentals. Still the scientist in me
> wants data backing assertions. As I understand it plpgsql works close to
> the server and is optimized to do so. I know writing in C would be a
> better solution. Still is calling plpgsql functions inside plpgsql
> really a bad thing when just considering plpgsql?
>
> The answer to that is the same answer to so many other things: it depends.
>
> plpgsql functions are slower than C. They also lack a lot of language
> features that C has. That being said, if they're meeting your needs, then
> don't worry about it. plpgsql is around because for most people, it works
> well enough. There are certainly cases when you want to create very complex
> logic in the database and plpgsql is liable to make that difficult. But
> there are a lot of cases where having to manage pointers and a build
> environment and all the things that go with C aren't justified, because
> plpgsql has none of that complexity. There are advantages both ways.
>
> The beauty of PostgreSQL is that you have both available and you
> can choose whichever is best for your situation.
>
> Agreed, though in my case I drop into plpythonu when I want more complex solutions.
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>
> --
> Melvin Davidson
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.