Syntax error in plpgsql

Question

I have a stored procedure in PostgreSQL

CREATE OR REPLACE FUNCTION show_senti_lang_setting(IN _senti_id bigint)
RETURNS TABLE(lang_code character, native_name character varying, is_active boolean) AS
$BODY$
BEGIN
RETURN QUERY 
    SELECT
        l.lang_code,
        l.native_name,
        (CASE WHEN s.senti_id is NULL THEN FALSE
            ELSE TRUE
        END) is_active
    FROM
        language l
    LEFT JOIN senti_lang s
    ON s.lang_code=l.lang_code
    AND s.senti_id=_senti_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;

The Error is:

ERROR:  syntax error at or near "$1"
LINE 1: ...HEN s.senti_id is NULL THEN FALSE ELSE TRUE END)  $1  FROM l...
                                                         ^
QUERY:   SELECT l.lang_code, l.native_name, (CASE WHEN s.senti_id is NULL THEN FALSE ELSE TRUE END)  $1  FROM language l LEFT JOIN senti_lang s ON s.lang_code=l.lang_code AND s.senti_id= $2 
CONTEXT:  SQL statement in PL/PgSQL function "show_senti_lang_setting" near line 13

********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "show_senti_lang_setting" near line 13

Seems like the error is due to CASE in plpgsql. The same function in is working well in SQL:

CREATE OR REPLACE FUNCTION show_senti_lang_setting(bigint)
    RETURNS TABLE(lang_code character, native_name character varying, is_active boolean) AS
$BODY$

    SELECT
        l.lang_code,
        l.native_name,
        CASE WHEN s.senti_id is NULL THEN FALSE
            ELSE TRUE
        END is_active
    FROM
        language l
    LEFT JOIN senti_lang s
    ON s.lang_code=l.lang_code
    AND s.senti_id=$1;

$BODY$
  LANGUAGE sql VOLATILE STRICT;

You **need** to supply an explanation in plain English what the function is supposed to do. I suspect it may not be doing what you think it does. — Erwin Brandstetter, Jan 05 '13 at 14:23

score 6 · Accepted Answer · edited May 23 '17 at 10:24

As to what you have:

Version 8.4 had a problem with OUT parameters of the same name as column aliases in the returned row (is_active). This has been amended by now, it works in PostgreSQL 9.1 or later (maybe even 9.0). That's the reason for your syntax error.

Column aliases are just noise in this constellation. They are discarded in favor of the declared OUT parameters. Their only purpose might be documentation, so just make it a comment and avoid conflicts to begin with:

    CASE WHEN s.senti_id is NULL THEN FALSE ELSE TRUE END -- AS is_active

Also:

You should use the keyword AS with column aliases (while it is generally ok to skip it for table aliases).
No parentheses needed around the CASE statement.

Better form

The way you have it, you always return all rows from table language - and one or more instances where a match in senti_lang is found. At the same time you define your function STRICT, so you get no rows at all when you provide a NULL value for _senti_id. It's very hard to imagine a sensible use case for that.

I don't expect you'd want to return multiple rows per language if multiple matches are found in senti_lang. So you can simplified to:

CREATE OR REPLACE FUNCTION show_senti_lang_setting(IN _senti_id bigint)
RETURNS TABLE(lang_code character, native_name varchar, is_active boolean) AS
$func$
BEGIN
RETURN QUERY 
   SELECT l.lang_code
         ,l.native_name
         ,EXISTS (SELECT 1 FROM senti_lang s
                  WHERE  s.lang_code = l.lang_code
                  AND    s.senti_id = _senti_id) -- AS is_active
   FROM   language l;
END
$func$ LANGUAGE plpgsql VOLATILE STRICT;

And I would raise the question whether it needs to be STRICT.

Meaning of `Select 1`

In reply to the follow-up question in comments below. I quote the manual on EXISTS:

Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally unimportant. A common coding convention is to write all EXISTS tests in the form EXISTS(SELECT 1 WHERE ...).

Basically you can write any syntactically valid expression. It is discarded anyway.
We have been discussing readability under this related question.

and about STRICT actually i'm still learning about postgresql. thanks for the mention about that, i will read from the documentation — Ahmad, Jan 06 '13 at 17:17
once again thanks, now i understand more than my first question. — Ahmad, Jan 06 '13 at 17:52

score 1 · Answer 2 · answered Jan 05 '13 at 13:48

this seems to be a name clash between the parameter and the column alias for the case.

If you rename the alias in the select query it should work

SELECT
    l.lang_code,
    l.native_name,
    (CASE WHEN s.senti_id is NULL THEN FALSE
        ELSE TRUE
    END) as active_flag  -- <### this is the change 
FROM
    language l
LEFT JOIN senti_lang s
ON s.lang_code=l.lang_code
AND s.senti_id=_senti_id;

SQLFiddle example: http://sqlfiddle.com/#!11/49075/1

The value of the case expression is mapped by the position to the "output" parameters. So they can have different names (and apparently have to).

Syntax error in plpgsql

2 Answers2

As to what you have:

Better form

Meaning of Select 1

Meaning of `Select 1`