Stored insert procedure in plpgsql
- by crazyphoton
I want to do something like this in PostgreSQL.
I tried this:
CREATE or replace FUNCTION create_patient(_name text, _email text, _phone text, _password text, _field1 text, _field2 text, _field3 timestamp, _field4 text, OUT _pid integer, OUT _id integer) RETURNS record AS $$
DECLARE
_id integer;
_type text;
_pid integer;
BEGIN
_type := 'patient';
INSERT into patients (name, email, phone, field1, field2, field3) values (_name, _email, _phone, _field1, _field2, _field3) RETURNING id into _pid;
INSERT into users (username, password, type, pid, phone, language) values (_email, _password, _type, _pid, _phone, _field4) RETURNING id into _id;
END;
$$ LANGUAGE plpgsql;
But there are a lot of instances where I would not want to specify some of field1/field2/field3/field4 and want the unspecified fields to use the default value in the table. Currently that is not possible, because to call this function I need to specify all fields.
TLDR; Is there a simple way to create a wrapper procedure for INSERT in PL/pgSQL where I can specify which fields I want to insert?
