Re: Function Question - Inserting into a table with foreign constraints - Mailing list pgsql-general

On 11/5/2011 10:35 AM, Raymond O'Donnell wrote:
> On 05/11/2011 04:34, Brandon Phelps wrote:
>> Hello all,
>>
>> Could someone give me an example as to how I would accomplish something
>> like this with a function?:
>>
>> 3 tables:
>>
>> tableA: id (serial), name (varchar), description (varchar), subcat_id
>> (integer)
>> tableB: id (serial), subcat_name (varchar), cat_id (integer)
>> tableC: id (serial), cat_name
>>
>> I would like to create a function (ie. stored procedure) that I can pass
>> 4 things:
>> name, description, subcat_name, cat_name
> To begin with, don't give the parameters the same names as columns in
> the tables you're going to be manipulating.
>
> create or replace function my_function(
>    p_name varchar,
>    p_description varchar,
>    p_subcat_name varchar,
>    p_cat_name varchar
> )
> returns void as
> $$
> declare
>    m_cat_id integer;
>    m_subcat_id integer;
> begin
>    ..... (see below)
>    return;
> end;
> $$
> language plpgsql;
>
>> When the procedure runs it would do the following:
>> 1. Check to see if cat_name exists in tableC
>>    a. if so, get the id
>>    b. if not, insert a new record into tableC using the supplied
>> cat_name, and get the id of the newly created record
> Assuming you've read up[1] on how to create a pl/pgsql function in the
> first place, declare variables, etc, it'd go something like this:
>
>    select id into m_cat_id from tablec where cat_name = p_cat_name;
>    if not found then
>      insert into tablec (cat_name) values (p_cat_name)
>      returning id into m_cat_id;
>    end if;
>
> Remember too that identifiers always fold to lower-case unless you
> double-quote them.
>
>> 2. Check to see if subcat_name exists in tableB where cat_id is the
>> value returned from step 1
>>    a. if so, get the id
>>    b. if not, insert a new record into tableB using the supplied
>> subcat_name and the cat_id returned from step 1, and get the id of the
>> newly created record
> Similar to above, but store the value in m_subcat_id.
>
>> 3. Insert a record into tableA with the name and description supplied to
>> the procedure, and the subcat_id returned from step 2
>    insert into tablea (name, description, subcat_id)
>    values (p_name, p_description, m_subcat_id);
>
> HTH,
>
> Ray.
>
> [1] http://www.postgresql.org/docs/9.1/static/plpgsql.html
>

Thanks for the quick reply Ray.  I had the notion of using IF statements
to check if the IDs in question existed already, but I figured there
might be a more fluid way of doing something like this without having a
bunch of extra logic.

With the method you outlined will I notice any huge performance
impacts?  The application would be parsing incoming data from another
3rd party application and could, at times, be executing the function in
very fast succession, although never twice at the exact same moment
(single threaded application, pending events will just block until
they're up).

Thanks again!

--
Brandon