Confused by misleading error message on SP creation

From: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Confused by misleading error message on SP creation
Date: 2007-01-09 11:42:47
Message-ID: 71E37EF6B7DCC1499CEA0316A256832802B3EB89@loki.wc.globexplorer.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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';

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-01-09 11:55:27 Re: Confused by misleading error message on SP creation
Previous Message Martijn van Oosterhout 2007-01-09 10:25:01 Array constructor requires one argument