Thread: Multiple inserts into 2 tables with FKs

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?

Greetings,
Tiemo.

On 7/3/07, Tiemo Kieft <t.kieft@gmail.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

Josh Tolley wrote:
> 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');
But what if the filename is already in the filenames table? It will give
an error due to duplicate values, so I'll have to do a SELECT after
that? I was kind of hoping there was a somewhat more elegant solution to
this.