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'); On Sat, May 2, 2015 at 7:37 PM, Adrian Klaver <adrian.kla...@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.kla...@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.kla...@aklaver.com >>>> <mailto:adrian.kla...@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.kla...@aklaver.com > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.