Question 167880 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql function syntax error near Language plpgsql;

I’m stuck with a plpgsql syntax error.

Here is the error message :

ERROR: syntax error on or near "LANGUAGE"
LINE 30: LANGUAGE plpgsql;

I solved a previous syntax error and after I changed my code I got a new one.

Here is my code :

drop function if exists get_person_and_subtable(integer);

CREATE OR REPLACE FUNCTION get_person_and_subtable(IN id integer)
RETURNS SETOF record 
AS $BODY$

DECLARE var_categ CHAR(5); 
DECLARE var_id INT = id;

BEGIN
    var_categ := (select PER_CATEG from T_PERSONNE where PER_ID = var_id);
    
    IF var_categ ='EMP' THEN RETURN QUERY 
        SELECT * FROM T_PERSONNE 
        INNER JOIN T_EMPLOYE ON T_PERSONNE.PER_ID = T_EMPLOYE.EMP_ID  
        WHERE PER_ID = var_id;
    END IF;
    IF var_categ ='PROSP' THEN RETURN QUERY 
        SELECT * FROM T_PERSONNE 
        INNER JOIN T_PROSPECT ON T_PERSONNE.PER_ID = T_PROSPECT.PROSP_ID  
        WHERE PER_ID = var_id;
    END IF;
    IF var_categ = 'VIS' THEN RETURN QUERY 
        SELECT * FROM T_PERSONNE
        INNER JOIN T_VISITEUR ON T_PERSONNE.PER_ID = T_VISITEUR.VIS_ID 
        WHERE PER_ID = var_id;
    END IF;
END;
$BODY$;
LANGUAGE plpgsql;
Tags:

2

Answers


  1. 0

    Remove semicolon after $BODY$:

    drop function if exists get_person_and_subtable(integer);

CREATE OR REPLACE FUNCTION get_person_and_subtable(IN id integer)
RETURNS SETOF record 
AS $BODY$

DECLARE var_categ CHAR(5); 
DECLARE var_id INT = id;

BEGIN
    var_categ := (select PER_CATEG from T_PERSONNE where PER_ID = var_id);
    
    IF var_categ ='EMP' THEN RETURN QUERY 
        SELECT * FROM T_PERSONNE 
        INNER JOIN T_EMPLOYE ON T_PERSONNE.PER_ID = T_EMPLOYE.EMP_ID  
        WHERE PER_ID = var_id;
    END IF;
    IF var_categ ='PROSP' THEN RETURN QUERY 
        SELECT * FROM T_PERSONNE 
        INNER JOIN T_PROSPECT ON T_PERSONNE.PER_ID = T_PROSPECT.PROSP_ID  
        WHERE PER_ID = var_id;
    END IF;
    IF var_categ = 'VIS' THEN RETURN QUERY 
        SELECT * FROM T_PERSONNE
        INNER JOIN T_VISITEUR ON T_PERSONNE.PER_ID = T_VISITEUR.VIS_ID 
        WHERE PER_ID = var_id;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;
    Login or Signup to reply.
  2. 0

    A misplaced semicolon is the immediate cause for the error message.

    But your problems won’t end there. You are trying to shoehorn three different queries with different result types into a single function. That’s borderline impossible. You fall back to RETURNS SETOF record, but that’s not a practical solution. To call this function you’d have to provide a column definition list with the call. So you would have to know beforehand, which of the three sub-tables will be joined. And if you know that already, there is no point in using a function with "dynamic" output. Catch 22. This is going nowhere.

    Use a single return type for a single function. And it rarely makes sense to return SETOF record to begin with. Return a well defined row type instead.

    Better yet, run this plain query instead:

    SELECT *
FROM   t_personne p
LEFT   JOIN t_employe  e ON p.per_categ = 'EMP'   AND p.per_id = e.emp_id 
LEFT   JOIN t_prospect o ON p.per_categ = 'PROSP' AND p.per_id = o.prosp_id
LEFT   JOIN t_visiteur v ON p.per_categ = 'VIS'   AND p.per_id = v.vis_id
WHERE  p.per_id = _id;

    That’s all.
    Columns for all three sub-tables will be included, just (at least) two of those are filled with null values. The point is to have a stable return type. You can wrap that into a function
    if you insist.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search