Re: Using a serial primary key as a foreign key in a second table

> You simply add the company to the database, get its ID, and then
> insert the
> person with the appropriate company_id. That is the simplest way
> to think
> about the process. This will work for as many concurrent users as
> you like.

The problem here is that the company_id is the only field that is
guaranteed to uniquely identify a company record: it's possible
(albeit unlikely) that there is another "Looney Tunes" in the company
table, but that one is the lesser-known Canadian company that
manufactures bird whistles, and Bugs Bunny doesn't work for them. As
a dumb human I can tell the difference (perhaps by looking at the
company address field) but my clever computer is more persnickety. So
I don't know how to identify the relevant record from which to "get
its ID" without knowing its ID!

Searching the internet, I found these two examples from an Oracle-
related site and am looking to implement something analagous that
works in postgres, but I'm new to PL/pgSQL and commands like
"nextval" so am struggling.

DECLARE
l_company_id companies.company_id%TYPE;
BEGIN
-- Select the next sequence value.
SELECT companies_seq.NEXTVAL
INTO l_company_id
FROM dual;

-- Use the value to populate the master table.
INSERT INTO companies (company_id, company_name)
VALUES (l_company_id, 'Looney Tunes');

-- Reuse the value to populate the FK link in the detail table.
INSERT INTO people (company_id, person_name)
VALUES (l_company_id, 'Bug Bunny');

COMMIT;
END;

DECLARE
l_company_id companies.company_id%TYPE;
BEGIN
-- Populate the master table, returning the sequence value.
INSERT INTO companies (company_id, company_name)
VALUES (companies_seq.NEXTVAL, 'Looney Tunes')
RETURNING company_id INTO l_company_id;

-- Use the returned value to populate the FK link in the detail
table.
INSERT INTO people (company_id, person_name)
VALUES (l_company_id, 'Bugs Bunny');

COMMIT;
END;

Can anyone tell me how to translate either (I prefer the latter) into
postgres-compliant SQL?

Many thanks, Nat

___________________________________________________________
Yahoo! Messenger - with free PC-PC calling and photo sharing. http://uk.messenger.yahoo.com