0

I have 10 state wise data tables. Table name like aa_vo_detail, ab_vo_detail, ac_vo_detail.

I have also main state table in which state short names and state codes are stored. I have created a postgresql function in which first I get state short name based on state code and concat it with _vo_detail and prepare table name. In second step after prepare table name I am using it in select, insert and update query. But I am getting error in second step.

Example: Suppose I have state code 2 the query fetch state short name from the state's main table and state short name is 'ab' for state code 2 so after concat short name with _vo_detail first function return ab_vo_detail table name and store it in another variable and using that variable in select, insert and update query.

CREATE OR REPLACE FUNCTION insertOrUpdateByTable()

RETURNS text AS $$ 

DECLARE 

    tablename text; shg nrlmshare.shg_detail_share%rowtype;

BEGIN 

   tablename := (select concat(lower(state_short_name), '_shg_detail') from 
main_state where state_code = '3');

   for shg in select * from nrlmshare.shg_detail_share         

loop

if (select count(shg_code) from tablename where  shg_code=shg.shg_code||'-'||left(shg.entity_code,2))=0  then

INSERT INTO tablename( shg_code,shg_type)VALUES(shg.shg_code,shg.shg_type);

else 

update tablename set shg_type=shg.shg_type where  shg_code=shg.shg_code||'-'||left(shg.entity_code,2);

end if;

end loop;

END;

$$  LANGUAGE plpgsql;

When I perform this function I have to insert or update in concatenated table name. But I am getting this error

ERROR:  relation "tablename" does not exist
LINE 1: SELECT (select count(shg_code) from tableName where  shg_cod...
4
  • 1
    "I have 10 state wise data tables. Table name like aa_vo_detail, ab_vo_detail, ac_vo_detail. - that is a really bad database design. You should have only one table vo_detail with a column named code. Commented Aug 27, 2019 at 12:07
  • You will need dynamic SQL for that but better fix your bad database design Commented Aug 27, 2019 at 12:08
  • Please tell me how to I use tablename for query in this situation. Right now I can't change database design. Please help me Commented Aug 27, 2019 at 12:11
  • See the my answer for your colleague: stackoverflow.com/questions/57671148 Commented Aug 27, 2019 at 12:12

1 Answer 1

1

This select concat(lower(state_short_name), '_shg_detail') from main_state where state_code = '3' probably return an invalid tablename or null value

What is the result of select concat(lower(state_short_name), '_shg_detail') from main_state where state_code = '3' in your db?

Sign up to request clarification or add additional context in comments.

1 Comment

This qeury return stateShortName_shg_detail table name. For state code 3 short name is ab so after concat with _shg_detail query return ab_shg_detail

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.