I want fetch data from different different table name using postgresql function

Question

I have 30 state wise data tables. Table name like aa_shg_detail, ab_shg_detail, ac_shg_detail.

I have also main state table in which state short names and state codes are stored. I have created 2 postgresql functions getTableName(Code text) and getDataByTable().

In the first function I pass the state code so it fetches the state short name and short name concat with _shg_detail String and prepare full table name and return it. Example: If I pass state code 2 the query fetch state short name based on state code 2 from the state's main table. The state short name is 'ab' for state code 2 so after concat state short name with _shg_detail first function return ab_shg_detail table name.

Second function gets the table name from first function and fetch data from that table. But I am getting error in the second function.

CREATE OR REPLACE FUNCTION getTableName(code text) 
  RETURNS text 
AS $$ 
   select concat(lower(state_short_name), '_shg_detail') from main_state where state_code = code)) 
$$  
LANGUAGE sql; 

CREATE OR REPLACE FUNCTION getDataByTable() 
  RETURNS text AS $$ 
DECLARE 
    tablename text; 
BEGIN 
   tablename := gettablename('2');
   RETURN (select shg_code from tablename);  
END;
$$  LANGUAGE plpgsql;

When I execute a second function select getDataByTable() then I am getting this error every time:

ERROR:  relation "tablename" does not exist
LINE 1: SELECT (select shg_code from tableName)

"I have 30 state wise data tables. Table name like aa_shg_detail, ab_shg_detail, ac_shg_detail" that is a really bad database design. You should only have a single table with a code column that contains rows from all the other tables. Then you don't need any functions at all and your query gets as easy as select shg_code from shg_detail where code = '2' — user330315
– user330315, Commented Aug 27, 2019 at 9:07

score 1 · Accepted Answer · 2019-08-27 10:21:39Z

1

You need dynamic SQL for that:

CREATE OR REPLACE FUNCTION getDataByTable() 
  RETURNS text AS $$ 
DECLARE 
    tablename text;
    l_result text; 
BEGIN 
   tablename := gettablename('2');
   execute format('select shg_code from %I', tablename)  
     into l_result;
   RETURN l_result;  
END;
$$  LANGUAGE plpgsql;

The %I placeholder of the format() function properly deals with quoting of identifiers if needed.

edited Aug 27, 2019 at 10:21

answered Aug 27, 2019 at 9:05

user330315

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

2 Comments

Rakesh Over a year ago

I am getting error after executing this. ERROR: syntax error at or near "(" LINE 7: RETURN execute format('select shg_code from %I', tablenam...

user330315 Over a year ago

@Rakesh: ah, sorry, I always forge that this shorthand only works for return query. See my edit.

Collectives™ on Stack Overflow

I want fetch data from different different table name using postgresql function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related