Step 2: Creating functions the business tool will use

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 tables

• Create and test procedures and functions

• Design application behavior

• Design user interface

• 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('per

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.

StackExchange Code Review Q#63098, answer score: 3