Confused by misleading error message on SP creation

["Gregory S. Williamson" <gsw@xxxxxxxxxxxxxxxx>]

This is PostgreSQL 8.1.4, and I am utterly lost.

This function started in Informix and worked perfectly. Migrating to postgres resulted in about a *10,000* x slow down as it resorts to doing a sequential scan. In the sql below the addition of client id and report id to the initial select are an attempt to force an index; without them the SQL always results in a dog of a plan.

The error message:
psql:e.sql:54: ERROR:  syntax error at or near "$2" at character 15
QUERY:  SELECT   $1   $2  :=  $3
CONTEXT:  SQL statement in PL/PgSQL function "client_year_usage_det" near line 29
psql:e.sql:54: LINE 1: SELECT   $1   $2  :=  $3
psql:e.sql:54:  


Is of *NO* use. Could someone please explain what the hell it means ? There is no construct $1 $2 := $3 *anywhere*. There is no SELECT on line 54. Or line 29. What the heck is going here ?

This is one of the few things about postgres that I absolutely despise. The SPL support is crappy and horribly hard to debug.

The hapless .sql file:
--CREATE TYPE clyud_t AS ( sp_rptdate DATE, sp_appid CHAR(10), sp_is_subs INTEGER, sp_use_siu INTEGER, sp_hits DECIMAL, sp_s
ius DECIMAL, sp_amount DECIMAL, sp_contractid INTEGER);
--DROP TYPE clyud_read_t;
--CREATE TYPE clyud_read_t AS (report_id INTEGER, sp_rptdate DATE, client_id CHAR(10), contract_id INTEGER, appid CHAR(10),
sp_is_subs INTEGER, sp_use_siu INTEGER, hits DECIMAL, sius DECIMAL, total_amnt DECIMAL);
CREATE OR REPLACE FUNCTION client_year_usage_det (CHAR(10), INTEGER, INTEGER) RETURNS SETOF clyud_t AS '
DECLARE
        p_client_id ALIAS FOR $1;
        p_year ALIAS FOR $2;
        p_showall ALIAS FOR $3;
        sp_year INTEGER;
        sp_tyr INTEGER;
        sp_sdate DATE;
        sp_edate DATE;
        sp_is_subs INTEGER;
        sp_use_siu INTEGER;
        clyud_rec clyud_t;
        clyu_inrec clyud_read_t;
BEGIN
IF ((p_year IS NULL) OR (p_year = 0)) THEN
        sp_year := (SELECT EXTRACT(YEAR FROM CURRENT_DATE));
ELSE
        sp_year := p_year;
END IF;
sp_tyr := sp_year + 1;
sp_sdate := sp_year || ''-'' ||''01-01'';
sp_edate := sp_tyr || ''-'' ||''01-01'';
RAISE NOTICE ''showall is %, sdate is % and edate is %'', p_showall, sp_sdate, sp_edate;
FOR clyu_inrec IN SELECT w.report_id,b.report_s_date,w.client_id,w.contract_id,w.appid,w.is_subscribed,w.use_sius,SUM(w.hits),SUM(w.sius),SUM(w.total_amnt) FROM reporting.crs_rpt_work w, reporting.billing_reports b WHERE w.report_id IN (SELECT b.report_id FROM reporting.billing_reports WHERE b.report_s_date >= sp_sdate AND b.report_s_date < sp_edate) AND w.client_id = p_client_id GROUP BY 1, 2, 3, 4, 5, 6, 7 ORDER BY 1 DESC, 2 DESC, 2, 3, 4 LOOP
  RAISE NOTICE ''a) date %  appid %   hits %'',clyu_inrec.sp_rptdate, clyu_inrec.appid, clyu_inrec.hits;
  clyud_rec.sp_rptdate := clyu_inrec.sp_rptdate;
  clyud_rec.sp_appid := clyu_inrec.appid;
  clyud_rec.sp_is_subs := clyu_inrec.sp_is_subs
  clyud_rec.sp_use_siu := clyu_inrec.sp_use_siu;
  clyud_rec.sp_hits := clyu_inrec.hits;
  IF (clyu_inrec.sp_use_siu <> 1) THEN
      clyud_rec.sius := clyu_inrec.hits;
  ELSE
      clyud_rec.sp_sius := clyu_inrec.sius;
  END IF;
  clyud_rec.sp_contractid := clyu_inrec.contract_id;
      RETURN NEXT clyud_rec;
  END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';