SELECT from tableA if not in tableC
IMHO I think that the fast way on this case is looking first at TableC, and if not exists on TableC, search the record on TableA/B.

I've set up a small example:



create table tableA (id int, path varchar(30), other_things int);
create table tableB (id int, path varchar(30), other_things int);
create table tableC (id int, path varchar(30), other_things int);
insert into tableA values 
(1, '/file1/account1/0001', 1),(2, '/file1/account2/0001', 1),
(3, '/file1/account1/0002', 1),(4, '/file2/account1/0002', 1),
(5, '/file1/account2/0003', 1),(6, '/file2/account1/0003', 1);
insert into tableB values 
(1, '/file1/account1/0001', 1),(2, '/file1/account2/0001', 1),
(3, '/file1/account1/0002', 1),(4, '/file1/account2/0002', 1),
(5, '/file2/account1/0003', 1),(6, '/file1/account1/0003', 1);
insert into tableC values 
(1, '/file3/account3/0004', 1),(2, '/file3/account4/0004', 1),
(3, '/file4/account3/0005', 1),(4, '/file3/account4/0005', 1),
(5, '/file3/account3/0006', 1),(6, '/file4/account3/0006', 1);



As far as you are using a regular expression /file/account/[0-9]+ to filter records, you could use the operator ~. (POSIX Regular Expressions) 

But have a look at this answer of Erwin Brandstetter about using regular expressions, just in case you could add some extra index to your table.

I've used a function because it allows me to use IF EXISTS (). It only search the record on TableA if it doesn't exists on TableC. Obviously you can change the search order.




CREATE OR REPLACE FUNCTION getABC (MyPath VARCHAR) 
 RETURNS TABLE (
 ID text,
 client_id text,
 other int
) 
AS $$
BEGIN

   IF EXISTS(SELECT 1 FROM TableC WHERE path ~ $1) THEN
        RETURN QUERY SELECT split_part(t1.path::text, '/'::text, 2)::text AS id,
                            split_part(t1.path::text, '/'::text, 3)::text AS clientid, 
                            t1.other_things
                     FROM   TableC t1
                     WHERE  t1.path ~ $1;
    ELSE
        RETURN QUERY SELECT split_part(t1.path::text, '/'::text, 2)::text AS id,
                            split_part(t1.path::text, '/'::text, 3)::text AS clientid, 
                            t1.other_things
                     FROM   TableA t1
                     JOIN   TableB t2
                     ON     t1.path = t2.path
                     WHERE  t1.path ~ $1;
    END IF;

    RETURN;
END; $$ 
LANGUAGE 'plpgsql';



Now you can search for a specific path on this way:




  This records are fetched from TableC

select * from getABC('/file3/account4/[0-9]+');



id    | client_id | other
:---- | :-------- | ----:
file3 | account4  |     1
file3 | account4  |     1






  This records are fetched from TableA

select * from getABC('/file1/account1/[0-9]+');



id    | client_id | other
:---- | :-------- | ----:
file1 | account1  |     1
file1 | account1  |     1



dbfiddle here