Re: Multiple inserts into 2 tables with FKs

On 7/3/07, Tiemo Kieft <t(dot)kieft(at)gmail(dot)com> wrote:
> Hi
>
> I have 2 tables, one containing unique filenames, the other containing
> paths to those files. Paths are spread over multiple hosts (SMB shared
> files). The layout is like this:
>
> Column | Type |
> Modifiers
> ---------------+------------------------+-----------------------------------------------------------------
> filename_id | integer | not null default
> nextval('filenames_filename_id_seq'::regclass)
> filename_name | character varying(512) |
> Indexes:
> "filenames_pkey" PRIMARY KEY, btree (filename_id)
> "filenames_filename_name_key" UNIQUE, btree (filename_name)
>
> Column | Type |
> Modifiers
> ------------------+-------------------------+---------------------------------------------------------
> path_id | integer | not null default
> nextval('paths_path_id_seq'::regclass)
> path_filename_id | integer |
> path | character varying(2048) |
> Foreign-key constraints:
> "paths_path_filename_id_fkey" FOREIGN KEY (path_filename_id)
> REFERENCES filenames(filename_id)
>
> The thing is, these tables will be update regularly (once a day, at
> least). All hosts on this SMB network are indexed, their files and paths
> added to the database if they aren't there already. My question is, how
> do I actually go about doing this? How do I find out which filename_id
> to insert into the paths table?

If I understand the problem correctly, you might do something like
this (if you're using 8.2 and have INSERT...RETURNING):

INSERT INTO filename (filename_name) VALUES ('some_filename_here')
RETURNING filename_id;

This will return a value, for instance, 10. Then you'd do this:

INSERT INTO path (path_filename_id, path) VALUES (10, 'your_path_here');

If you're on an earlier version than 8.2, you don't have
INSERT...RETURNING. You will have to do select from your filename
table to find the filename_id for the filename you've just inserted,
or you could do it all within one transaction (which you probably
should do anyway), and use CURRVAL(''filenames_filename_id_seq') to
find the latest inserted filename_id value.

-Josh