CODEWITHSUNDEEP
postgresqlpostgresql-10
Shubham Pradhan
Shubham Pradhan

Reputation: 31

Postgresql Function is not working properly

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

Upvotes: 0

Views: 182

Answers (1)

rinomau
rinomau

Reputation: 1428

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?

Upvotes: 1

Related Questions