What's better to create a function in plpgsql (postgresql) or on a python class?

Question

I have this problem, I don't know if I should make the functions on Postgresql DB with plpgsql or if I should make it inside a class on python/java whatever language programming might work.

My boss asked me why I was writing the functions on plpgsql and not on python and I coulnd't give him a reliable answer of why it was better.

I have google it and I couldn't find any information about this. The only thing that I can imagine about doing the function on plpgsql is that if I change of programming language the only thing that I would need to do is to call the function and not rewriting it on the new language.

This is an example of a plpgsql that I write with the help of a stackoverflow member, should I rewrite it in a python class or leave it inside the db as a function and just call the function from a python class?

CREATE OR REPLACE FUNCTION createid(idslist varchar[], objecttype varchar)
RETURNS TABLE(original_id varchar, new_id varchar) as
$$
declare 
l_prefix text;
BEGIN
IF LOWER(objectType) = 'global' THEN
    l_prefix := 'GID';
ELSE
    l_prefix := 'ORG';
END IF;
RETURN QUERY
INSERT INTO idstable(original_id, new_id)
select t.x, l_prefix||nextval('mapSquema.globalid')::TEXT
from unnest(idslist) as t(x)
returning *
END;
$$ LANGUAGE plpgsql;

Good question. But there's no right answer. Google for "business logic in database vs application". Some people think it's bad practice. But I don't totally agree. It's all about tradeoffs and the architecture. — pumbo
– pumbo, Commented Nov 3, 2017 at 4:00

Pavel Stehule · Accepted Answer · 2017-11-03 04:32:42Z

2

There are some advantages of stored procedures generally:

is possible to minimize moving data to client side,
is possible to reduce network traffic,
the server side code is accessible simply from different environments,
it helps with decomposition on data processing part and data presentation part,
with secure definer functions you can increase a security to level impossible without stored procedures.

Sometimes these features are interesting and helps to develop very fast and well maintained applications.

answered Nov 3, 2017 at 4:32

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

john-bright Over a year ago

I don't know if what I think about the loop is correct or not, but correct me if I'm wrong. If I make the function in plpgsql it will call one time to the database while if I make the function on python and loop through the array, it will make "len(array)" calls to the db? If so, then the function on plpgsql is better or not?

Pavel Stehule Over a year ago

It depends what do you do. The loops has same speed, but there are some overheads - data format translations, network overhead related to data processing on client side. It is removed when you use stored procedures. This overhead is pretty significant - more times I increase speed by ten times just to using stored procedures.

Collectives™ on Stack Overflow

What's better to create a function in plpgsql (postgresql) or on a python class?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related