Step 2: Creating functions the business tool will use

Question

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.

1. Design the DB schema and table relationships, and insert data for standards tables
2. Create and test procedures and functions
3. Design application behavior
4. Design user interface
5. Design export methods and formats

One 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 function

set 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 void
language plpgsql 
as $$
begin
set 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 function

A few notes...

1. 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.

2. 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.

3. 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.

• Craig Ringer: "create function is the Postgres version of create procedure". Er, no, it isn't. PostgreSQL simply doesn't support "procedures", it only has functions. Some drivers emulate procedures by calling a function of the same name, e.g. PgJDBC's {? = call funcname(?) } support. Many other RDBMSs support functions as well as procedures.
• Craig Ringer: "I opted for snake_case because Postgres silently converts all table, column and function names to lower case". Yes, it follows the SQL standard (well, mostly; the SQL spec actually requires upper case). If you want to preserve case, use spaces, etc you can "Double Quote" your identifiers.
• Craig Ringer: Overall, I think the biggest unanswered question here is why there's a function at all. What does this gain you over simply inserting from the application? There's a lot to be said for using functions for non-trivial logic, but this isn't. Also, you don't use a RETURNING clause to capture the person_id generated in the first insert, so person_id is never defined for later inserts.
• Phrancis: @CraigRinger I am trying to explicitly avoid "Double Quotes" since it is a PITA to always use those. But if you do think it is best practices, feel welcome to answer the question as such.
• Alex L: Erg - I think I see first_name and last_name! I thought we learned something about doing that. ;)

Answer 1

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 void
language plpgsql 
SET search_path = PsychoProductions
AS $$
...

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.

Answer 2

I'm slightly puzzled by the intended usage of this function. If there is a one-to-one correspondence between a person and the address, e-mail, and phone number, then perhaps they should be lumped into one big table. On the other hand (which is more likely), if there can be multiple addresses, e-mails, and phone numbers per person, then it should be possible to insert the supplementary information à la carte.

The function should return the Person.id of the newly inserted person's record.

• Phrancis: It is the latter case (one-to-many correspondence) I'm not sure how one would go about adding them à la carte... Any suggestion?

Answer 3

If your goal with the function is to force each person to have at least one phone number and one address (while allowed to have more than one), then you need to ensure that your permissions are set so that the only way to perform an insert into the person table is through this function. After all, your application is not the only way to make modifications to the database.

Otherwise, you might want to consider using a deferred constraint trigger instead. As long as your child tables have corresponding records at the end of the transaction, you're good:

CREATE OR REPLACE FUNCTION at_least_one_is_required() RETURNS TRIGGER AS $$
 BEGIN
 	-- repeat for each child table as necessary
 	PERFORM 1 FROM child_table WHERE fk_col = NEW.parent_col LIMIT 1;
 	IF NOT FOUND THEN
 		RAISE 'insert or update on parent_table (all parent_tables records must have at least 1 record in child_table)';
 	END IF;
 	-- end repeat
 	RETURN NEW;
 END;
 $$ LANGUAGE 'plpgsql';
CREATE CONSTRAINT TRIGGER at_least_one_is_required AFTER INSERT ON parent_table INITIALLY DEFERRED
 FOR EACH ROW EXECUTE PROCEDURE at_least_one_is_required();

If you're dead set on using a function and require the ability to pass in multiple phone numbers/addresses per person, then you'll need to use arrays. I like to create few composite types if the data being passed in doesn't match the table columns, but you're free to use your table instead (after all, a table is a type!).

CREATE TYPE _phone AS (type TEXT, number TEXT);
CREATE OR REPLACE FUNCTION new_person(_name TEXT, _role TEXT, _phone _PHONE)
 returns INTEGER
 language plpgsql 
AS $$
DECLARE
 _person_id TEXT;
BEGIN
 -- insert person (abbreviated)
 INSERT INTO person
 (name, role)
 VALUES
 (_name, _role)
 RETURNING id INTO _person_id;
 -- now insert the phone info
 INSERT INTO person_phone_numbers
 (person_id, type, number)
 SELECT
 _person_id,
 p.type,
 p.number
 FROM
 unnest(_phone) AS p;
COMMIT;
RETURN _person_id;
END;
$$ VOLATILE;
SELECT new_person
 ( 'Bob from Accounting'
 , 'Accountant'
 , ARRAY
 [ ('Home', '555-1234')
 , ('Work', '555-5555')
 , ('Cel', '555-9876')
 ] :: _PHONE ARRAY
 );