Concatenate multiple fields in query string in plpgsql

Question

I am using plpgsql and hibernate and want to create a function which contains the query string given below. In the select clause I want to concatenate 3 fields but while running this query I am getting error message like:

ERROR: syntax error at or near "' '"
SQL state: 42601
Context: PL/pgSQL function "est_fn_dept_wise_emp_report" line 30 at open

I am new using stored functions, it might be a basic question but somehow I was unable to find a solution.

 query1 = 'SELECT  est_emp_empmaster.emp_no AS est_emp_empmaster_emp_no,
            adm_m_department.dept_name AS adm_m_department_dept_name,
            adm_m_subdepartment.sub_dept_id AS adm_m_subdepartment_sub_dept_id,
            adm_m_subdepartment.sub_dept_name AS adm_m_subdepartment_sub_dept_name,
            est_m_designation.desig_name AS est_m_designation_desig_name,
            est_emp_empmaster.first_name'|| ' ' ||'est_emp_empmaster.middle_name'|| ' '               ||'est_emp_empmaster.surname AS empname
    FROM public.adm_m_department adm_m_department
        INNER JOIN public.adm_m_subdepartment adm_m_subdepartment
        ON adm_m_department.dept_id = adm_m_subdepartment.dept_id
        INNER JOIN public.est_emp_empmaster est_emp_empmaster
        ON adm_m_department.dept_id = est_emp_empmaster.dept_id
        AND adm_m_subdepartment.sub_dept_id = est_emp_empmaster.sub_dept_id
        INNER JOIN public.est_emp_salary est_emp_salary
        ON est_emp_empmaster.emp_no = est_emp_salary.emp_no
        INNER JOIN public.est_m_designation est_m_designation
        ON est_emp_salary.pre_desig_code = est_m_designation.desig_code
        AND est_emp_salary.retired_flag ='|| quote_literal('N') ||'
         WHERE   est_emp_empmaster.corp_coun_id=0 or est_emp_empmaster.corp_coun_id is null or est_emp_empmaster.corp_coun_id = '|| quote_literal($1) ||'
         ORDER BY adm_m_department.dept_id,adm_m_subdepartment.sub_dept_id,est_emp_empmaster.emp_no ASC';


    OPEN refcur FOR
         EXECUTE query1;
    LOOP
         FETCH refcur INTO return_record;
         EXIT WHEN NOT FOUND;
         RETURN NEXT return_record;
    END LOOP;
    CLOSE refcur;**

The above query runs fine if I execute it without executing through query string. But as I want to use this query for multiple conditions and in those condition I want to modify this query to get different results.

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2013-02-13T12:12:12

This can be much simpler, safer and faster (assuming at least Postgres 8.4):

 CREATE OR REPLACE FUNCTION foo(_corp_coun_id int) -- guessing type
  RETURNS TABLE (
    emp_no        int  -- guessing data types ..
   ,dept_name     text -- .. replace with actual types
   ,sub_dept_id   int
   ,sub_dept_name text
   ,desig_name    text
   ,empname       text) AS
$func$
BEGIN

RETURN QUERY
SELECT em.emp_no
      ,dp.dept_name
      ,sb.sub_dept_id
      ,sb.sub_dept_name
      ,ds.desig_name
      ,concat_ws(' ', em.first_name, em.middle_name, em.surname) --  AS empname
FROM   adm_m_department    dp
JOIN   adm_m_subdepartment su ON sb.dept_id = dp.dept_id
JOIN   est_emp_empmaster   em ON em.dept_id = sb.dept_id 
                             AND em.sub_dept_id = sb.sub_dept_id
JOIN   est_emp_salary      sl ON sl.emp_no = em.emp_no
                             AND sl.retired_flag = 'N'    -- untangled join cond.
JOIN   est_m_designation   ds ON ds.desig_code = sl.pre_desig_code
WHERE  em.corp_coun_id = 0 OR
       em.corp_coun_id IS NULL OR
       em.corp_coun_id = $1
ORDER  BY dp.dept_id, sb.sub_dept_id, em.emp_no;

END    
$func$
  LANGUAGE plpgsql SET search_path=public;

To address your primary question: use concat_ws() for simple and secure concatenation of multiple columns (doesn't fail with NULL).
You do not need dynamic SQL here since the variables are only values (not identifiers).
You do not need a CURSOR here.
You do not need a LOOP. RETURN QUERY does the same, simpler and faster.
You do not need column aliases, only the names of the OUT parameters (implicitly the column names in RETURNS TABLE (...)) are relevant.
Replace the multiple schema qualification public. in your query with a single SET search_path = public.
I also untangled your query, used short table aliases and reformatted to make it easier to read.
You don't even need plpgsql at all here. Can be a simpler SQL function:

 CREATE OR REPLACE FUNCTION foo(_corp_coun_id int)
  RETURNS TABLE (
    emp_no        int  -- guessing data types ..
   ,dept_name     text -- .. replace with actual types!
   ,sub_dept_id   int
   ,sub_dept_name text
   ,desig_name    text
   ,empname       text) AS
$func$
SELECT em.emp_no
      ,dp.dept_name
      ,sb.sub_dept_id
      ,sb.sub_dept_name
      ,ds.desig_name
      ,concat_ws(' ', em.first_name, em.middle_name, em.surname) --  AS empname
FROM   adm_m_department    dp
JOIN   adm_m_subdepartment sb ON sb.dept_id = dp.dept_id
JOIN   est_emp_empmaster   em ON em.dept_id = sb.dept_id 
                             AND em.sub_dept_id = sb.sub_dept_id
JOIN   est_emp_salary      sl ON sl.emp_no = em.emp_no
                             AND sl.retired_flag = 'N'    -- untangled join cond.
JOIN   est_m_designation   ds ON ds.desig_code = sl.pre_desig_code
WHERE  em.corp_coun_id = 0 OR
       em.corp_coun_id IS NULL OR
       em.corp_coun_id = $1
ORDER  BY dp.dept_id, sb.sub_dept_id, em.emp_no;
$func$
  LANGUAGE sql SET search_path=public;

Concatenate multiple fields in query string in plpgsql

2 Answers