Why use a stored procedure instead of querying directly in db?

Question

My company is pursuing a new policy because my company will certify some international standards. This policy is that the database administrator is not allowed to directly query the data in the database, for example:

select * from some_table, update some_table, etc. 

We must use the stored procedure to execute these queries.

Regarding my last question here: Postgres pl / pgsql ERROR: column "column_name" does not exist

I am wondering if a stored procedure needs to be created for each table or for each condition? Is there a way to create stored procedures more efficiently?

Thanks for your reply earlier ..

and sorry for my bad english ..: D

Answer 1

Some reasons for using stored procedures are:

• They presumably underwent some testing to make sure they did not violate business rules, as well as some optimization for performance.
• They ensure consistent results. Each time you are asked to complete task X, you run the stored procedure associated with task X. If you write a request, you may not write it the same way each time; maybe one day you will forget something stupid, like forcing text before comparing, and something is missing.
• They begin to write a little more than just a request, but starting this stored procedure takes less time than writing the request again. Run it enough time, and it becomes more efficient to write a stored procedure.
• They reduce or eliminate the need to know base table relationships.
• You can grant permissions to execute stored procedures (using the security definer ), but deny permissions for base tables.
• Programmers (if you separate database administrators from programmers) can provide the API, and all they need to know. As long as you support the API when changing the database, you can make the necessary changes to the basic relationship without breaking their software; in fact, you don’t even need to know what they did with your API.

You will likely create one stored procedure for each request that you would otherwise have executed.

I'm not sure why you find this inefficient or especially time consuming compared to just writing a query. If all you do is put the request inside a stored procedure, the extra work should be minimal.

 CREATE OR REPLACE FUNCTION aSchema.aProcedure ( IN var1 text, IN var2 text, OUT col1 text, OUT col2 text ) RETURNS setof record LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY DEFINER SET search_path = aSchema, pg_temp AS $body$ BEGIN RETURN QUERY /*the query you would have written anyway*/; END; $body$; GRANT EXECUTE ON FUNCTION aSchema.aProcedure(text, text) TO public; 

As you used in your previous question, a function can be even more dynamic by passing columns / tables as parameters and using EXECUTE (although this increases how much the person performing the function needs to know how this function works, so I try to avoid this )

If the "less efficient" comes from the additional logic included in the function, then comparison with just using queries is unfair, since the function does the extra work.