I've been working on this project and here is my first function for step 2. I feel this is the most important one, as I will carry over recommendations from this review into multiple other functions that affect more tables & data. Design the DB schema and table relationships, and insert data for standards tablesCreate and test procedures and functionsDesign application behaviorDesign user interfaceDesign export methods and formatsOne note...For those who are used to more mainstream RDBMS... PostgreSQL simply doesn't support procedures, it only has functions. A function can be made to behave like a procedure and that is what I'm doing here. Creating the functionset search_path = PsychoProductions;create or replace function fcn_insert_person(        -- person table        prm_role_id text,         prm_first_name text,         prm_last_name text,         prm_organization text,         prm_website text,         prm_default_billing_method_id text,         prm_active boolean,         -- address table        prm_address_type_id text,         prm_address text,         prm_city text,         prm_state text,         prm_zip_code text,         -- email table        prm_email_address text,         prm_email_type_id text,         -- phone table        prm_phone_number text,         prm_phone_type_id text)returns voidlanguage plpgsql as $$beginset search_path = PsychoProductions;insert into person (        role_id,        first_name,        last_name,        organization,        website,        default_billing_method_id,        active        )values (        prm_role_id,        prm_first_name,        prm_last_name,        prm_organization,        prm_website,        prm_default_Billing_Method_id,        prm_active        );insert into address (        person_id,        address_type_id,        address,        city,        state,        zip_code        )values (        ( select currval('person_id_seq') ),        prm_address_type_id,        prm_address,        prm_city,        prm_state,        prm_zip_code        );insert into email (        person_id,        email_address,        email_type_id        )values (        ( select currval('person_id_seq') ),        prm_email_address,        prm_email_type_id        );insert into phone (        person_id,        phone_number,        phone_type_id        )values (        ( select currval('person_id_seq') ),        prm_phone_number,        prm_phone_type_id        );end;$$Query returned successfully with no result in 70 ms.Using the functionA few notes...The following is a saved template (temporary solution, will be called within an application later). It passes values to the function. I had originally opted to just list the values in the order they are expected, based on the create function statement. But then after asking on StackOverflow I found good reasons to explicitly enumerate parameters being passed to the function. So, I opted for the more verbose but less error-prone parameter := value syntax. I opted for snake_case because Postgres silently converts all table, column and function names to lower case. I feel that prm_default_Billing_Method_id is better than prmdefaultbillingmethodid. Additional note: I used a function because I wanted to make sure the whole transaction got processed in one step, inserting data into all 4 tables before it commits. Executing the function:set search_path = PsychoProductions;select fcn_insert_person (        -- person table        prm_role_id := 'Customer',                 /*                     Staff                    Partner                    Customer                    Vendor                    Session musician                */        prm_first_name := 'firstname',         prm_last_name := 'lastname',         prm_organization := 'organization',         prm_website := 'website',         prm_default_billing_method_id := 'Net 30',                /*                    Unassigned                    Net 30                    Net 15                    Cash on delivery                    Cash with order                */        prm_active := True,            -- address table        prm_address_type_id := 'Unique',                 /*                    Unique                    Physical                    Shipping                    Billing                    Mailing                */        prm_address := 'address',         prm_city := 'city',         prm_state := 'state',         prm_zip_code := 'zip',         -- email table        prm_email_address := 'email',         prm_email_type_id := 'Business',                /*                    Business                    Personal                */        -- phone table        prm_phone_number := 'phone',         prm_phone_type_id := 'Mobile'                /*                    Mobile                    Business                    Home                    Fax                    Pager                */  );Total query runtime: 257 ms.1 row retrieved.
Step 2: Creating functions the business tool will use
sql;postgresql;stored procedure
You haven't clearly shown why you need a function/procedure at all, but I'll assume it's a set requirement - i.e. someone said Thou Shalt Use Procedures As A Database API.(Edit after update): A function is no different, in terms of transaction processing, to simply:BEGIN;INSERT ....;INSERT ....;INSERT ....;COMMIT;If there are race conditions or issues with doing it with top-level SQL, there are the same issues with a function. The only differences are that a function is implicitly wrapped in a transaction if none is already open wheras top-level SQL will run each statement in its own autocommitting transaction, and that to use savepoints (subtransactions) functions must use BEGIN ... EXCEPTION blocks, instead of explicit SAVEPOINT and ROLLBACK TO SAVEPOINT commands.Assuming that a function is warranted, then what you're doing is mostly OK. However, you don't capture the ID generated by inserting the person and use it for subsequent inserts; instead you're relying on currval. That's OK, it won't cause problems, but it's not lovely. You probably want to:...AS $$DECLARE   _person_id integer;BEGIN    insert into person (            ...blah...            )    values (            ...blah...            )    RETURNING person_id    INTO STRICT _person_id;to store the generated key in _person_id for re-use. Then pass that in the subsequent VALUES clauses.Additionally, you shouldn't SET search_path within the function body. It'll confuse callers, and doesn't take effect early enough. Instead, add it to the function declaration:...returns voidlanguage plpgsql SET search_path = PsychoProductionsAS $$...A few other notes:PostgreSQL doesn't support procedures at all. It doesn't have a CALL statement, it doesn't have procedures that can commit and begin transactions, return multiple result sets, etc. Instead, it just has very powerful functions with support for returning sets. True procedure support is strongly desired, and hopefully will be added one day. Especially if somebody funds work on supporting multiple result sets and autonomomous transactions. Currently, people emulate procedures with functions and drivers convert procedure-call syntax like JDBC's { call procname(?) } into a SELECT function().PostgreSQL case-folds unquoted identifiers because that's what the SQL spec says to do. DBMSs that fail to do so are violating the spec. Pg does differ from the spec in that it case-folds to lower case where the spec says upper case, though. To prevent case folding and allow any character in an identifier you may Double Quote your identifiers. In practice, though, I prefer to simply use lower case identifiers with underscores where appropriate as it's more convenient and often more readable.