Re: plpgsql functions organisation

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(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/02/2015 03:28 PM, Bill Moran wrote:
>
>> On Sat, 02 May 2015 15:06:24 -0700
>> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>
>> On 05/02/2015 02:07 PM, Jeff Janes wrote:
>>>
>>>> On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver <
>>>> adrian(dot)klaver(at)aklaver(dot)com
>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.