Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--https://stackoverflow.com/q/78227674/5298879
--Compare fields Where One is Similar to Part of Another in Postgres sql

--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.


create table tblNames1 (ID,	Description,	target_table) as values
 (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')
;
create table tblNames2(ID,	Description,	target_table) as values
 (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,'%');

SELECT 4



SELECT 4




id
description
target_table
id
description
target_table



SELECT 0


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



SELECT 4


--none of these are tracked because of quoted definition `as $f$`
create table table1 (abc int, def text);
create function f() returns void language sql as $f$
select abc,def from table1 $f$;
create function f(a int) returns setof table1 language sql as $f$
select abc,def from table1 $f$;
create function f(a table1) returns setof table1 language sql as $f$
select abc,def from table1 $f$;

select * from information_schema.routine_table_usage where routine_name='f';
select * from information_schema.routine_column_usage where routine_name='f';

CREATE TABLE



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION




specific_catalog
specific_schema
specific_name
routine_catalog
routine_schema
routine_name
table_catalog
table_schema
table_name



SELECT 0




specific_catalog
specific_schema
specific_name
routine_catalog
routine_schema
routine_name
table_catalog
table_schema
table_name
column_name



SELECT 0


--these are tracked
create function f2(a table1) returns setof table1 language sql 
return (select table1 from table1);

create function f3(a table1, b table1) returns setof table1 language sql 
begin atomic select abc,def from table1; end;

create function f4(a table1) returns setof int language sql 
return (select abc from table1);

select * from information_schema.routine_table_usage 
  where routine_name in ('f2','f3','f4');
select * from information_schema.routine_column_usage 
  where routine_name in ('f2','f3','f4');

CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION




specific_catalog
specific_schema
specific_name
routine_catalog
routine_schema
routine_name
table_catalog
table_schema
table_name


postgres
public
f2_16402
postgres
public
f2
postgres
public
table1

postgres
public
f3_16403
postgres
public
f3
postgres
public
table1

postgres
public
f4_16404
postgres
public
f4
postgres
public
table1



SELECT 3




specific_catalog
specific_schema
specific_name
routine_catalog
routine_schema
routine_name
table_catalog
table_schema
table_name
column_name


postgres
public
f3_16403
postgres
public
f3
postgres
public
table1
abc

postgres
public
f3_16403
postgres
public
f3
postgres
public
table1
def

postgres
public
f4_16404
postgres
public
f4
postgres
public
table1
abc



SELECT 3

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

specific_catalog	specific_schema	specific_name	routine_catalog	routine_schema	routine_name	table_catalog	table_schema	table_name
postgres	public	f2_16402	postgres	public	f2	postgres	public	table1
postgres	public	f3_16403	postgres	public	f3	postgres	public	table1
postgres	public	f4_16404	postgres	public	f4	postgres	public	table1