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
asit resorts to doing a sequential scan. In the sql below the addition of client id and report id to the initial select
arean 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*.
Thereis 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)
FROMreporting.crs_rpt_work w, reporting.billing_reports b WHERE w.report_id IN (SELECT b.report_id FROM
reporting.billing_reportsWHERE b.report_s_date >= sp_sdate AND b.report_s_date < sp_edate) AND w.client_id =
p_client_idGROUP 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';