1

I would like to write a Select query where I can see if one column is like part of another column in different table postgresql.

tblNames1:

ID Description target table
1 select test.fn_hierarchy_prod_group test_1
2 select run_update_query test_2
3 select func_datad_addr_1 test_3
4 select func_datad_addr test_4

tblNames2:

ID Description target table
1082 fn_hierarchy_prod_group dba.l
1091 func_datad_addr dba.n
1099 fn_hierarchy_customer dba.m
1100 run_update_query dba.j

The query should return and update target tblnames1:

ID Description target table
1 select test.fn_hierarchy_prod_group dba.l
2 select run_update_query dba.j
3 select func_datad_addr_1 test_3
4 select func_datad_addr dba.n

I am new to postgres and have search different ways to update it but I am not getting success.I have tried using query like

select *
from tblnames1 a 
left join tblnames2 b on 1=1 
where b.target_table like concat('%',a.target_table,'%')

But this query is wrong.

Improve this question
1
  • What’s your end goal? If those are view definitions, there are system catalogs and views that can tell you which view depends on which tables. As to your query, it looks like you want to join on a regex with the Description column in it, not the target_table. Depending on where those select statements in the other description column come from, you might have a bad time matching them if they use varying case and combine that with varying double quoting. Commented Mar 27, 2024 at 6:43

1 Answer 1

Reset to default
0
  1. According to the schema you showed, you want to join on regex-matched Description columns, not target_tables.
  2. You want the shorter Description with just the object identifier to be in the regular expression, and look for that in the longer Description with the SQL select statements in it.

Demo at db<>fiddle:

select a.id, a.description, b.target_table
from tblNames1 a 
left join tblNames2 b
  on a.description ilike concat('%',b.description,'%');
id description target_table
1 select test.fn_hierarchy_prod_group dba.l
2 select run_update_query dba.j
3 select func_datad_addr_1 dba.n
4 select func_datad_addr dba.n
  1. You probably want case-insensitive ilike, not like.
  2. Depending on where those select statements in the other Description column come from, you might have a bad time matching them if they use varying case and combine that with varying double quoting (unquoted are folded to lowercase, quoted preserve original case) - in that case ilike will be wrong.
  3. In PostgreSQL 14+, some view/routine/column dependencies can be found in information_schema views with _usage in their name if that's what you're after. Some more insight could be acquired in the system catalogs. The demo shows how plain SQL functions with unquoted bodies 
    create function f() returns text BEGIN ATOMIC SELECT result FROM table1; END;
create function f() returns text RETURNS (SELECT result FROM table1);
    get listed in routine_table_usage. Unfortunately, it doesn't track those with quoted bodies 
    create function f() returns setof int AS $f$SELECT result FROM table1$f$;
    so you do need to run regular expressions against the routine definition in those cases.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much for sharing ,I have created another question with similar details but some more requirements...can you pls help me with that..stackoverflow.com/questions/78245542/… solution is really helpful and i am using it as starting point however I am working on building an overall solution because pattern of function like select from function name will remain same so I am thinking to build solution by removing selectfrom and keeping function name while comparing exact match.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.