Okay, so today shall commence my goal to at least be able to post a single blog per day. In our show for today (*wink), i am very honoured to be of help in your quest to building stored procedures for your app (should you ever use it).
I came across a lot of references and tutorials in order to fully grasp how stored procedures work or are built. It was tough work because i didn’t know where to start. Today, i’ll be sharing to you a basic stored procedure file with basic CRUD operation that I finally came up with after series of refactoring, mess and research of course.
Here’s a bit of a background.
In our Software Engineering class, we are using Flask in building our APIs, along with stored procedures directly tailored within our Postgresql DB for faster performance. We are on an agile approach and tests are a priority (behaviour-driven development, hence we use lettuce). For the front end, I intend to use Angular within my team, for faster development. Learning the back-end technologies alone are taking up time, and has a steep learning curve. Anyway, our project’s quite interesting – we’re building an e-commerce platform!
Let’s go back to stored procedures, shall we?
So, CRUD. Create, Read, Update and Delete. The basic operations that your app should ever perform. This file first came up looking like this:
-- Stored procedures for inventory
-- Create table for products
create table products (
product_id int primary key,
title text,
description text,
date_added timestamp,
ordering int,
supplier_id int,
category_id int,
site_id int,
product_type_id int,
on_hand int,
re_order_level int,
is_active boolean
);
-- Function: Create new product
-- Sample: select * from get_products();
create or replace function new_product(in par_product_id int, in par_title text, in par_description text, in par_date_added TIMESTAMP, in par_ordering int, in par_supplier_id int, in par_category_id int, in par_site_id int, in par_product_type_id int, in par_on_hand int, in par_re_order_level int, in par_is_active boolean) returns text as
$$
declare
loc_product_id text;
loc_res text;
begin
select into loc_product_id product_id from products where product_id = par_product_id;
if loc_product_id isnull then
insert into products(product_id, title, description, date_added, ordering, supplier_id, category_id, site_id, product_type_id, on_hand, re_order_level, is_active) values (par_product_id, par_title, par_description, par_date_added, par_ordering, par_supplier_id, par_category_id, par_site_id, par_product_type_id, par_on_hand, par_re_order_level, par_is_active);
loc_res = 'ok';
else
loc_res = 'id exists';
end if;
return loc_res;
end;
$$
language 'plpgsql';
-- Create Sample Product
select new_product(100, 'title', 'description', '1999-01-08 04:05:06', 0, 1, 1, 1, 1, 100, 10, true);
-- Function: Get all product
create or replace function get_products(out int, out text, out text, out timestamp, out int, out int, out int, out int, out int, out int, out int, out boolean) returns setof record as
$$
select * from products;
$$
language 'sql';
-- Function: Get single product. Input: ID
-- Sample: select * from get_product_id(2);
create or replace function get_product_id(in par_product_id int, out int, out text, out text, out timestamp, out int, out int, out int, out int, out int, out int, out int, out boolean) returns setof record as
$$
select product_id, title, description, date_added, ordering, supplier_id, category_id, site_id, product_type_id, on_hand, re_order_level, is_active from products where product_id = par_product_id;
$$
language 'sql';
-- Function: Update single product. Input: ID
create or replace function update_product_id(in par_product_id int, in par_title text, in par_description text, in par_date_added TIMESTAMP, in par_ordering int, in par_supplier_id int, in par_category_id int, in par_site_id int, in par_product_type_id int, in par_on_hand int, in par_re_order_level int, in par_is_active boolean) returns text as
$$
declare
loc_product_id text;
loc_res text;
begin
update products set
title = COALESCE(par_title, title),
description = COALESCE(par_description, description),
date_added = COALESCE(par_date_added, date_added),
ordering = COALESCE(par_ordering, ordering),
supplier_id = COALESCE(par_supplier_id, supplier_id),
category_id = COALESCE(par_category_id, category_id),
site_id = COALESCE(par_site_id, site_id),
product_type_id = COALESCE(par_product_type_id, product_type_id),
on_hand = COALESCE(par_on_hand, on_hand),
re_order_level = COALESCE(par_re_order_level, re_order_level),
is_active = COALESCE(par_is_active, is_active)
where product_id = par_product_id;
loc_res = 'ok';
return loc_res;
end;
$$
language 'plpgsql';
So yes, we already got a create, read, and update functionality working there. Then eventually, i’m settling for this:
---------------------------------------------------------------------------
--
-- Execute this script before before running the app
-- Stored procedures for inventory
-- Database: Postgresql
--
-- Author: Roselle Ebarle
-- http://roselleebarle.com
--
-----------------------------------------------------------------
create table products (
product_id serial primary key,
title text,
description text,
date_added timestamp without time zone NOT NULL DEFAULT now(),
ordering int DEFAULT 0,
supplier_id int,
category_id int,
site_id int,
product_type_id int,
on_hand int,
re_order_level int,
is_active boolean DEFAULT TRUE
);
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION products_upsert(in par_product_id int, in par_title text, in par_description text, in par_supplier_id int, in par_category_id int, in par_site_id int, in par_product_type_id int, in par_on_hand int, in par_re_order_level int) returns text as $$
DECLARE
loc_response text;
BEGIN
-- If no product_category_id was passed, then create a new row else update the row
if par_product_id isnull then
insert into products(title, description, supplier_id, category_id, site_id, product_type_id, on_hand, re_order_level) values (par_title, par_description,par_supplier_id, par_category_id, par_site_id, par_product_type_id, par_on_hand, par_re_order_level);
loc_response = 'ok';
else
update products set title=par_title, description=par_description, supplier_id=par_supplier_id, category_id=par_category_id, site_id=par_site_id, product_type_id=par_product_type_id, on_hand=par_on_hand, re_order_level=par_re_order_level where product_id=par_product_id;
loc_response = 'ok';
end if;
return loc_response;
END;
$$ LANGUAGE 'plpgsql';
--------------------------------------------------------------------------------------------------------
select products_upsert(null, 'test','description',1,1,1,1,100,10);
--------------------------------------------------------------------------------------------------------
create or replace function products_get(in par_product_id int) returns setof products as $$
BEGIN
if par_product_id isnull then
return query select * from products;
else
return query select * from products where product_id = par_product_id;
end if;
END;
$$ LANGUAGE 'plpgsql';
-----------------------------------------------------------------
-- select * from products_get();
-----------------------------------------------------------------
I hope we have the same itch to eliminate as much redundancies as possible, and write the cleanest code of all. In the final version, we got the update and create (upsert) functionality merged. Of course, there’s still a lot of room for improvements, and i might be posting more soon. So let me know what you think, eh?
For easy reference, and tracking, here’s a Github Repository where i’ve placed the script. – Git
