Обсуждение: Function Question - Inserting into a table with foreign constraints
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 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 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 3. Insert a record into tableA with the name and description supplied to the procedure, and the subcat_id returned from step 2 In the end, when my app calls the procedure I'd like it to automatically create records in tables tableC and tableB if the _name fields don't already exist, then insert the primary record into tableA using the foreign key IDs from the other table. I'd like to do it this way because cat_name is unique in tableC, and (subcat_name, cat_id) are singularly unique in tableB. (ie. there can be multiple subcat_names as long as they belong to different categories from tableC). Any help would be greatly appreciated. Thanks, Brandon
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
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
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
On Nov 5, 2011, at 10:46, Brandon Phelps <bphelps@gls.com> wrote: > On 11/5/2011 10:35 AM, Raymond O'Donnell wrote: >> On 05/11/2011 04:34, Brandon Phelps wrote > > With the method you outlined will I notice any huge performance impacts? The application would be parsing incoming datafrom another 3rd party application and could, at times, be executing the function in very fast succession, although nevertwice at the exact same moment (single threaded application, pending events will just block until they're up). > > Thanks again! > You would probably be much better off importing the third-party data into a staging table, performing all of your "key" creation,and then inserting the data into your final tables. While triggers would work you end up with a lot of pointless effort when in most cases keys are likely to exist. David J.
On 05/11/2011 14:46, Brandon Phelps wrote: > > 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). I honestly don't know... your best bet would be to benchmark and see. I'd guess that any changes to the code within the function would have less effect than local conditions - disk I/O, processor speed, etc. AIUI, putting your code into a function has the advantage that the Potgres only has to load pl/pgsql and parse the function once for any given connection, and then the plan is reused for all subsequent invocations coming from that connection. If the function is only going to be called once per connection, then you have the overhead of loading pl/pgsql each time it is called, on top of parsing the function; so you might be better off trying to do this outside of a function altogether. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie