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.

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.

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
    );