Re: Function Question - Inserting into a table with foreign constraints

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(at)iol(dot)ie