SELECT from tableA if not in tableC I have a view, which selects data from TableA (TableA will be decommissioned once we migrated it all). We are creating a new table, called TABLEC, where we'll be migrating all the data from TableA to TableC in the future.


The reason we are doing this, it's because tableA is very old and bad designed.
At the moment, tableA is being used only for old records. All new data that would be inserted into tableA is already going to tableC via application.
I cannot change any table's schema.
The view can be changed to use CTE, IF, it is at least as fast as it's now.
path is not a real path, but it's a regular expression. It will be something like: /file/account/[0-9]+ &lt;-- after this there are some possibilites, it can be several different words (such as: attach/assigned/etc).


While we don't migrate all the data from tableA to tableC, we need the view to be compatible with both tables, as there is already new data going to tableC.

View:

CREATE OR REPLACE VIEW path_view AS
  SELECT split_part(n1.path::text, '/'::text, 18)::integer AS id,
         split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
         lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
         '00000000-1000-1000-3000-600000000000'::uuid AS guid,
         n1.bytes AS byte_count,
         n1.last_modified AS last_modified
  FROM   tablea n1
  JOIN   tableb s2 
  ON     s2.path = n1.path; 


-- tablea and tableb are pretty much the same; as said before, it was bad designed.

I need the view to do:


Look if the requested data is in the new table tableC
IF NOT, then go to tableA and get the data


QUESTION:

How can I do that? I tried with UNION ALL but it's very slow.

\d tableC:

Table "public.tablec"
--------------------+-----------------------------+-------------------------------------------------------------------
id            | integer                     | not null default nextval('tablec_id_seq'::regclass)
e_type        | integer                     | not null
e_id          | bigint                      | 
e_variation   | character varying(16)       | not null
path          | character varying(255)      | not null
name          | character varying(255)      | not null
size          | bigint                      | not null
md5           | md5_hash                    | not null
modified_date | timestamp without time zone | default statement_timestamp()
created_date  | timestamp without time zone | default statement_timestamp()
clientid      | bigint                      | not null
f_id          | bigint                      |
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