How to concat two string in postgresql function?

Question

I want a function which will return concated string. I am getting following error after execute this function in postgresql.

CREATE OR REPLACE FUNCTION getTableName () 
RETURNS text AS $$ 
DECLARE 
    state_short_name text; 
BEGIN 
   state_short_name := (select lower(state_short_name) from mst_state where state_code in (SELECT substr(entity_code,1,2) FROM shg_detail_share WHERE entity_code = '3420006002001'))   
   RETURN (CONCAT(state_short_name, '_shg_detail'));
END;
$$  LANGUAGE plpgsql

I expect the output like 'jh_shg_detail' but I am getting error like this

ERROR:  syntax error at or near "("
LINE 9:    RETURN (CONCAT(state_short_name, '_shg_detail'));

score 7 · Accepted Answer · 2019-08-26 13:07:23Z

You should use a select into in PL/pgSQL. And to avoid a name clash, don't name variables the same as columns:

CREATE OR REPLACE FUNCTION gettablename() 
RETURNS text AS $$ 
DECLARE 
    l_state_short_name text; 
BEGIN 
   select lower(state_short_name) 
      into l_state_short_name 
   from mst_state 
   where state_code in (SELECT substr(entity_code,1,2) 
                        FROM shg_detail_share 
                        WHERE entity_code = '3420006002001'));   
   RETURN CONCAT(state_short_name, '_shg_detail');
END;
$$  LANGUAGE plpgsql;

But you don't need PL/pgSQL for a simple SQL query like that. Your sub-query isn't really necessary as well. You can simplify that to where state_code = '34'

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

Your code is good as usual, but you didn't get the reason for the error.

Laurenz Albe · Accepted Answer · 2019-08-26 13:03:21Z

2

Your problem is a missing semicolon at the line with the assignment statement :=.

This makes the line that starts with RETURN (CONCAT a continuation line of the statement, and so you get the syntax error reported in that line.

answered Aug 26, 2019 at 13:03

Laurenz Albe

257k22 gold badges310 silver badges386 bronze badges

Collectives™ on Stack Overflow

How to concat two string in postgresql function?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related