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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
