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');
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.
В списке pgsql-general по дате отправления: