Syntax error in SQL statement of PL/pgSQL function

Question

I have written a stored procedure with the help of the SO community. I have cobbled/hacked together the answers of various questions to write my function.

However, when I try to create my function in the db (PostgreSQL 8.4), I get the following errors:

ERROR:  syntax error at or near "$4"
LINE 1: ...ank() OVER (ORDER BY  $1 ,  $2 ) /  $3 )::int as  $4  , $1 ,...
                                                            ^

AND

QUERY:   SELECT ceil(rank() OVER (ORDER BY  $1 ,  $2 ) /  $3 )::int as  $4  , $1 ,  $2 ,  $5 ,  $6 ,  $7 ,  $8 ,  $9 ,  $10  FROM foobar WHERE  $2  BETWEEN  $11  AND  $12  AND  $1  = ANY( $13 ) ORDER BY  $1 ,  $2 ,  $4 
CONTEXT:  SQL statement in PL/PgSQL function "custom_group" near line 9

This is the code for the function I am trying to create:

CREATE OR REPLACE FUNCTION custom_group(start_date DATE, end_date DATE, grouping_period INTEGER, _ids int[] DEFAULT '{}')
RETURNS TABLE (
grp INTEGER,
id INTEGER, 
entry_date DATE,
pop REAL,
hip REAL,
lop REAL,
pcl REAL,
vop BIGINT,
poi BIGINT) AS
$BODY$
BEGIN

IF _ids <> '{}'::int[] THEN -- excludes empty array and NULL
    RETURN QUERY 
                    SELECT ceil(rank() OVER (ORDER BY id, entry_date) / $3)::int as grp
                          ,id, entry_date, pop, hip, lop, pcl, vop, poi
                    FROM   foobar
                    WHERE  entry_date BETWEEN start_date AND end_date AND id = ANY(_ids)
                    ORDER  BY id, entry_date, grp ;

ELSE
    RETURN QUERY 
                    SELECT ceil(rank() OVER (ORDER BY id, entry_date) / $3)::int as grp
                          ,id, entry_date, pop, hip, lop, pcl, vop, poi
                    FROM   foobar
                    WHERE  entry_date BETWEEN start_date AND end_date
                    ORDER  BY id, entry_date, grp ;

END IF;

END;
$BODY$ LANGUAGE plpgsql;

Can anyone understand why I am getting these errors - and how do I fix them?

I happened to remember that I had encountered such cryptic error messages in the past, and I manage to resolve that by using different field names for the output table. I applied the same 'hack' this time and I was able to create the function with no errors. However, since I can't seem to locate this behaviour in the docs, I am guessing it's a hack - and I'll wait till someone who actually knows the reason for this odd behaviour to point me in the right direction. — Homunculus Reticulli
– Homunculus Reticulli, Commented Jul 23, 2012 at 15:17

Erwin Brandstetter · Accepted Answer · 2012-07-23 17:25:20Z

The error comes from a naming conflict.

The variable grp is defined implicitly by the RETURNS TABLE clause. In the function body, you try to use the same identifier as column alias, which conflicts.

Just use a different name for grp - the column alias will not be visible outside of the function anyway.

And table-qualify the other columns:

CREATE OR REPLACE FUNCTION custom_group(_start_date DATE
                                       ,_end_date DATE
                                       ,_grouping_period INTEGER, 
                                       ,_ids int[] DEFAULT '{}')
RETURNS TABLE (grp int, id int, entry_date date, pop real, hip real,
               lop real, pcl real, vop bigint, poi bigint) AS
$BODY$
BEGIN

IF _ids <> '{}'::int[] THEN -- excludes empty array and NULL
    RETURN QUERY 
    SELECT ceil(rank() OVER (ORDER BY f.id, f.entry_date) / $3)::int AS _grp
          ,f.id, f.entry_date, f.pop, f.hip, f.lop, f.pcl, f.vop, f.poi
    FROM   foobar f
    WHERE  f.entry_date BETWEEN _start_date AND _end_date AND id = ANY(_ids)
    ORDER  BY f.id, f.entry_date, _grp;

ELSE
    RETURN QUERY 
    SELECT ceil(rank() OVER (ORDER BY f.id, f.entry_date) / $3)::int -- no alias
          ,f.id, f.entry_date, f.pop, f.hip, f.lop, f.pcl, f.vop, f.poi
    FROM   foobar f
    WHERE  f.entry_date BETWEEN _start_date AND _end_date
    ORDER  BY f.id, f.entry_date, 1; -- ordinal pos. instead of col alias
END IF;

END;
$BODY$ LANGUAGE plpgsql;

The reason why I prefix IN parameters with _ is the same: avoid such naming conflicts.

You don't even have to use an alias for the computed column at all in this case. You could use the ordinal position in ORDER BY like I demonstrate in the second query. I quote the manual here:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

+1 for the tip on leading underscores on inbound parameters to avoid naming conflicts.

Collectives™ on Stack Overflow

Syntax error in SQL statement of PL/pgSQL function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related