What is faster postgresql procedures or sql queries to the database from the application?

Question

PostgreSQL

I don't know exactly how to ask the question.
Is there any difference when accessing the database from the application?
I use POSTGRESQL, DAPPER, .NET Core
For example, I create a fetch function (function postgres):

spoiler

CREATE FUNCTION gradebook.transcript_traditional_dislist(group_id_ bigint)
RETURNS TABLE(
  id bigint,
  dis_id bigint,
  group_id bigint,
  sem integer,
  method integer,
  ugod_id bigint,
  create_iin character varying,
  create_date date,
  create_time time without time zone,
  gr character varying,
  index_ru character varying,
  name_ru character varying
)
LANGUAGE SQL 
AS $$
SELECT m.*,up_d.index_ru,up_d.name_ru FROM gradebook.mains as m
LEFT OUTER JOIN uchast.uplans_dises as up_d ON up_d.id=m.dis_id 
WHERE m.group_id=group_id_ ORDER BY up_d.name_ru ASC
$$;

and access it through the application like this:

spoiler

(await conn.QueryAsync<TranscriptTraditionalDislist>(@"
SELECT * FROM gradebook.transcript_traditional_dislist(@group_id)", new { group_id = group_id })).ToList();

or directly from the application with a request:

spoiler

(await conn.QueryAsync<TranscriptTraditionalDislist>(@"
SELECT m.*,up_d.index_ru,up_d.name_ru FROM gradebook.mains as m
LEFT OUTER JOIN uchast.uplans_dises as up_d ON up_d.id=m.dis_id 
WHERE [email protected]_id ORDER BY up_d.name_ru ASC", new { group_id = group_id })).ToList();

Is there a difference in terms of security, in terms of performance, etc.? how to use?

Answer 1

1. Small savings, due to the fact that you do not need to send a long request over the network, but only a short procedure call (relatively)
2. In terms of security, there is no difference if an attacker has access to the executable file / source.
3. Of the potential conveniences - you can change the request without affecting the application.
So choose the method that suits you best.

Answer 2

In terms of convenience, the option is better directly from the application with a request - the logic is not divided into several places and it is easier to understand and edit later. There will be almost no difference in speed here.
Now, if you had a procedure that makes several requests, copies something, inserts / updates, in general, such logic that would require several requests instead of calling a function / procedure, then, of course, the procedure / function will work much faster and there is already a balance between convenience for maintaining the project and the speed of executing requests.