below is my postgres procedure
create or replace procedure ds_rs.test_ad () as $$
declare
i integer;
v_command text;
rcd record;
v_count text; v_pk_1 text; v_pk_2 text;
v_account_id varchar(100);
v_query text;
begin
for rcd in ( select t.table_name as object_name, a.table_schema, a.table_name from TLDEPLOY.TABLES_TO_VERIFY t
left join information_schema.tables a on concat
(COALESCE(a.table_schema, ''),'.',COALESCE(a.table_name, '')) = t.table_name order by t.table_name) loop
select count(*), max(column_name), min(column_name) into v_count, v_pk_1, v_pk_2
from information_schema.table_constraints c
join information_schema.columns cl on cl.table_name =c.table_name
where c.constraint_type ='PRIMARY KEY'
and c.table_name =rcd.table_name
and c.TABLE_SCHEMA=rcd.table_schema
and cl.ordinal_position =1
and cl.TABLE_SCHEMA=rcd.table_schema ;
v_command :=('insert into ds_rs.test_table (pk_1,pk_2) select '||v_pk_1||' , '||md5(v_pk_2::text)||' from ds_rs.account');
execute v_command;
end loop;
end; $$
language plpgsql;
while insert into table this query -> md5(v_pk_2::text) -> hash column name and inserts - i want column values to hash and insert
when used as normal - select md5(account_id::text) from account it gives correct value - but when passed as variable it inserts column name not column data
