By John Doe October 26, 2022
Sometimes it’s useful to bring data into code, and other times, it’s useful to bring code into data.
Stored procedures and Application code
A question that software developers often struggle with is whether a class of business logic should be implemented in a stored procedure implementation or in application code.
By putting business logic into a stored procedure implementation, developers worry about dependencies on database products. There is no SQL standard for the syntax of stored procedures, and database vendors have their own syntax definitions and special implementations for stored procedures. If the business access traffic and data volume increase in the future, the database needs to be redistributed, and the stored procedure needs to be converted into application code. Many developers with experience in the Internet industry generally have such worries.
Some large Internet companies, due to insufficient manpower investment and controllable business traffic, chose to put business logic into stored procedures at the beginning, and quickly built an early product business architecture. When the business has developed to a certain scale, the stored procedure has to be converted into application code because the business architecture is no longer applicable and the business database pays the cost. Since then, R&D regulations have been established, and to completely abandon database stored procedures is like suffering from “stored procedure phobia”.
In the traditional IT industry, some software vendors do not reject the use of stored procedures, mainly for several reasons:
- Application software products based on stored procedures, more versatile. For the customer’s customized requirements, only need to add or modify the stored procedure implementation at the customer’s site, and do not need to modify the code release version.
- The more versatile the application software product, the more customers the business covers, and the better the enterprise development.
- The more versatile the application software product, the fewer software developers need to be hired, because there is no need to frequently modify the code distribution.
- The labor cost of training and hiring a staff member who can write stored procedures is often lower than that of a software developer.
Object oriented programming
Among the software developer community, object-oriented programming ideas are very popular. When they finish constructing an interface or abstract class, they often have a heartfelt joy, and they can’t wait to tell the world immediately: Look, what a perfect abstract class I just constructed, and when the boss asks me to implement the relevant business requirements in the future, I just need to easily add a concrete implementation class!
However, no matter how advanced object-oriented programming thinking is, in the end, you still need to modify the code release to meet the new business needs.
Code is data
PostgreSQL’s procedural language gives software developers a new ability to implement business logic in a database using a regular programming language. You can even apply object-oriented programming ideas in combination with the basic capabilities of the relational model of the database.
Let’s use an example to show how to store code in a database based on object-oriented programming ideas:
CREATE DOMAIN scheme_code AS TEXT CHECK(VALUE ~ '^\(.*\)$');
CREATE FUNCTION calc(code scheme_code, x numeric)
RETURNS numeric
AS $$
(apply (eval (read (open-input-string code)))
(list x))
$$ LANGUAGE plscheme;
CREATE TABLE shape (
type text,
length scheme_code,
area scheme_code
);
The table shape
includes the following columns:
Name | Description |
---|---|
type |
The name of the shape |
length |
Code that calculates the length of the shape |
area |
Code that calculates the area of a shape |
We can insert information about various shapes into the data table shape
.
type | length | area |
---|---|---|
triangle | (lambda (x) (* x 3)) | (lambda (x) (/ (* x x) 2)) |
square | (lambda (x) (* x 4)) | (lambda (x) (* x x)) |
circle | (lambda (r) (* 2 3.14 r)) | (lambda (r) (* 3.14 r r)) |
For more details on PL/Scheme, see PL/Scheme documentation.