SQL - Dynamic Lookup with Regex

Question

I would like to determine the ownership of a folder. Therefore every folder has a constant naming convention, which is stored in the table OWNER(ident_string). Using the ident_string I want to determine the owner_id and write it (update) into table FOLDER(owner_id).

I have following tables in Postgresql:

create table owner(
  owner_id serial PRIMARY KEY,
  owner_name varchar(100),
  ident_string varchar(100));

create table folder(
 folder_id serial PRIMARY KEY,
 folder_name varchar(80),
 folder_path varchar(800),
 owner_id integer references owner(owner_id));

insert into owner (owner_name, ident_string) values ('Jonny English','b-jonny');
insert into owner (owner_name, ident_string) values ('Hanna Babara','b-hanna');
insert into owner (owner_name, ident_string) values ('Mary Marmelade','b-mary');

insert into folder (folder_name,folder_path) values ('b-jonny-20130101','/archive/backup/b-jonny-20130101');
insert into folder (folder_name,folder_path) values ('b-jonny-20130103','/archive/backup/b-jonny-20130103');
insert into folder (folder_name,folder_path) values ('b-hanna-20140101','/archive/backup/b-jonny-20140101');
insert into folder (folder_name,folder_path) values ('b-mary-20120303','/archive/backup/b-mary-20120303');

I think the only possiblity to do so is via PL/pgSQL:

iterate for folder_name in FOLDER over every row in OWNER
check for every ident_string to lookup the owner_id.

Could somebody help me out?

Drew · Answer 1 · 2014-10-04T13:11:02.810

2

Maybe something similar to the following?

update folder set folder.owner_id = owner.owner_id from folder join owner on folder_name like owner.ident_string + '%'

(like is the method for regexs in sql)

edited Oct 04 '14 at 13:11

answered Oct 04 '14 at 12:15

Drew

2,583
5
36
54

Hm, I'm not sure how that would work: I somehow need to match folder.folder_name with '%owner.ident_string%' in order to get owner.ownerid from the matched row and hen update the corresponding row in folder.owner_id.. – royskatt Oct 04 '14 at 12:40
Thank's for the solution, that was actually what i was asking for. Well, what is faster? I don't know, will test it. – royskatt Oct 04 '14 at 13:18
Actually I take that back about it being faster! http://stackoverflow.com/questions/6142235/sql-like-vs-performance – Drew Oct 04 '14 at 13:23

score 1 · Accepted Answer · answered Oct 04 '14 at 12:45

1

Try this:

update folder as f
   set owner_id = o.owner_id
  from owner as o
 where o.ident_string = left(f.folder_name,length(o.ident_string));

-g

answered Oct 04 '14 at 12:45

Greg

6,571
2
27
39

score 1 · Answer 3 · answered Oct 04 '14 at 14:17

You could create table folder in one sentence:

insert into folder (folder_name,folder_path,owner_id)
with tmp (folder_name,folder_path) as
(
select 'b-jonny-20130101','/archive/backup/b-jonny-20130101' union all
select 'b-jonny-20130103','/archive/backup/b-jonny-20130103' union all
select 'b-hanna-20140101','/archive/backup/b-jonny-20140101' union all
select 'b-mary-20120303','/archive/backup/b-mary-20120303'
)
select folder_name,folder_path,o.owner_id
  from tmp
  join owner o
    on tmp.folder_name ~ o.ident_string;
select * from owner;

Use a regular expression (~), it's more powerful.

See SQL FIDDLE DEMO

SQL - Dynamic Lookup with Regex

3 Answers3