why use stored procedure instead of query directly to db?

Question

My company doing new policy because my company would have certification of some international standards. That policy is, the DBA not allowed to query directly into database, like :

select * from some_table, update some_table, etc. 

We have to use stored procedure to do that queries.

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

I'm wondering, do we have to create a stored procedure per table, or per condition? Is there any way to create stored procedures more efficiently?

Thanks for your answer before..

and sorry for my bad english.. :D

Answer 1

Some reasons to use stored procedures are:

• They have presumably undergone some testing to ensure that they do not allow business rules to be broken, as well as some optimization for performance.
• They ensure consistency in results. Every time you are asked to perform task X, you run the stored procedure associate with task X. If you write the query, you may not write it the same way every time; maybe one day you forget something silly like forcing text to the same case before a comparison and something gets missed.
• They start off taking somewhat longer to write than just a query, but running that stored procedure takes less time than writing the query again. Run it enough times and it becomes more efficient to have written the stored procedure.
• They reduce or eliminate the need to know the relationships of underlying tables.
• You can grant permissions to execute the stored procedures (with security definer), but deny permissions on the underlying tables.
• Programmers (if you separate DBAs and programmers) can be provided an API, and that’s all they need to know. So long as you maintain the API while changing the database, you can make any changes necessary to the underlying relations without breaking their software; indeed, you don’t even need to know what they have done with your API.

You will likely end up making one stored procedure per query you would otherwise execute.

I'm not sure why you consider this inefficient, or particularly time-consuming as compared to just writing the query. If all you are doing is putting the query inside of 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, the function can be even more dynamic by passing columns/tables as parameters and using EXECUTE (though this increases how much the person executing the function needs to know about how the function works, so I try to avoid it).

If the "less efficient" is coming from additional logic that is included in the function, then the comparison to just using queries isn't fair, as the function is doing additional work.