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';
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
