Hi. I got an error message reported to me that I've never seen before, and
I'm not quite sure what it means or what would cause it. When I re-run the
query now, it runs without complaint, so the problem seems to have gone
away. Which of course I don't understand either! Would be nice to know
for the future. This is on 9.2.5.
The message says the error is at line 195 of a function. I've attached the
function source in case it's relevant/helpful.
(And BTW, how exactly is that line number counted? Does the "CREATE
FUNCTION" statement count as line 1? Or does it start with the opening
quote? And it looks like whitespace and comments are preserved internally,
so is it safe to assume the line number should match with a source file
used to create the function? My best guess is that line 195 in this case
is "pay_test.is_deleted=false;" but that doesn't help me understand this
error any better!
CREATE FUNCTION blah blah $$
DECLARE... Line 1?
CREATE FUNCTION blah blah
$$
DECLARE... Line 1? 2?
CREATE FUNCTION blah blah
$$DECLARE... Line 1?
Here's the query:
INSERT INTO tbl_payment SELECT * FROM generate_payments
('12/1/2013','ASSIST_PRI','3852',sys_user()) WHERE NOT
ROW(client_id,payment_type_code,payment_date) IN (SELECT
client_id,payment_type_code,payment_date FROM payment_valid);
The error message was:
ERROR: type of parameter 70 (text) does not match that when preparing the
plan (unknown) CONTEXT: PL/pgSQL function
generate_payments(date,text,integer,integer) line 195 at assignment
Thanks in advance for shedding any light on this.
Ken
--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ <http://agency-software.org/>*
[email protected]
(253) 245-3801
Subscribe to the mailing
list<[email protected]?body=subscribe>
to
learn more about AGENCY or
follow the discussion.
CREATE OR REPLACE FUNCTION pro_rate_amount(amount float, p_start date, p_end date) returns decimal(7,2)
language plpgsql AS $$
-- Function to calculate SPC pro-rate amounts
-- Can handle multiple months
-- Doesn't round--need to do it yourself
DECLARE
result float = 0;
tmp_date date;
work_start date;
work_end date;
argcnt int = 1;
chrcnt int = 0;
fmtlen int;
CHR text;
output text = '';
BEGIN
work_start = p_start;
work_end = p_end;
-- If multiple months, break into separate pieces and call recursively
LOOP
IF (date_part('month',work_start) <> date_part('month',work_end)) or (date_part('year',work_start) <> date_part('year',work_end)) THEN
tmp_date = date_trunc('month',work_start) + '1 month - 1 day';
result = result + pro_rate_amount(amount,work_start,tmp_date);
work_start = tmp_date + 1;
ELSE
EXIT;
END IF;
END LOOP;
result = result + (amount / days_in_month(work_start) * (work_end - work_start + 1));
RETURN result::decimal(7,2);
END;
$$;
--CREATE OR REPLACE FUNCTION generate_payments ( date, text[], int[] ) RETURNS SETOF record AS $FUNC$
CREATE OR REPLACE FUNCTION generate_payments ( date, text, int, int ) RETURNS SETOF tbl_payment AS $FUNC$
DECLARE
mdate ALIAS FOR $1;
mtype ALIAS FOR $2;
mclient ALIAS FOR $3;
by_who ALIAS FOR $4;
mdate_text text;
quer_assist text;
quer_assist_pri text;
quer_security text;
quer_utility text;
quer_utility_pr text;
quer_assist_x text;
quer_assist_pri_x text;
quer_security_x text;
quer_utility_x text;
quer_utility_pr_x text;
duplicate_clause text;
final_query text;
union_clause text;
cid_clause text;
payment record;
-- pay_test tbl_payment_test%rowtype;
pay_test tbl_payment%rowtype;
BEGIN
-- Define ASSISTANCE query
quer_assist = $$
SELECT
ro.client_id,
'%'::date AS payment_date,
'ASSIST'::text AS payment_type_code,
rent_amount_spc AS amount,
ro.housing_project_code,
ro.housing_unit_code,
ri.grant_number_code,
NULL AS comment,
vendor_number
-- FROM residence_own ro
FROM residence_own_current ro
LEFT JOIN rent_info ri ON
(ri.client_id=ro.client_id AND
'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
WHERE '%' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'%')
AND ro.is_active_manual
$$;
-- Define UTILITY query
quer_utility := $$
SELECT
ro.client_id,
'%'::date AS payment_date,
'UTILITY'::text AS payment_type_code,
0 - rent_amount_tenant AS amount,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
NULL AS comment,
tenant_vendor_number AS vendor_number
-- FROM residence_own ro
FROM residence_own_current ro
LEFT JOIN rent_info ri ON
(ri.client_id=ro.client_id AND
'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
WHERE '%' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'%')
AND ro.is_active_manual
AND rent_amount_tenant <= -5 -- Under $5 utility payments are not issued.
$$;
-- Define SECURITY query
quer_security := $$
SELECT
ro.client_id,
'%'::date AS payment_date,
'SECURITY'::text AS payment_type_code,
security_deposit AS amount,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
NULL AS comment,
vendor_number
-- FROM residence_own ro
FROM residence_own_current ro
LEFT JOIN rent_info ri ON
(ri.client_id=ro.client_id AND
'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
-- WHERE ro.residence_date BETWEEN ('%'::date - '1 month'::interval)::date AND ('%'::date -1)
-- For Security and prior payments, test whether record was added during this period
WHERE ( (ro.added_at > target_date_effective_at()) AND (ro.residence_date < '%'::date) )
AND ro.is_active_manual
AND (NOT ro.tenant_pays_deposit)
AND COALESCE(security_deposit,0) > 0
-- Test for moved out or not, for security deposit?
--AND '%' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'%')
$$;
-- Define ASSIST_PRI query
quer_assist_pri := $$
SELECT
ro.client_id,
'%'::date AS payment_date,
'ASSIST_PRI'::text AS payment_type_code,
--ROUND(rent_amount_spc / days_in_month(ro.residence_date) * ('%' - ro.residence_date))::decimal(7,2),
ROUND(pro_rate_amount(rent_amount_spc,ro.residence_date,target_date()-1)) AS amount,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
'Tenant pro-rated amount = $' || ROUND(pro_rate_amount(rent_amount_tenant,ro.residence_date,target_date()-1))::text AS comment,
vendor_number
-- FROM residence_own ro
FROM residence_own_current ro
LEFT JOIN rent_info ri ON
(ri.client_id=ro.client_id AND
'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
--WHERE ro.residence_date BETWEEN ('%'::date - '1 month'::interval)::date AND ('%'::date -1)
-- For Security and prior payments, test whether record was added during this period
WHERE ( (ro.added_at > target_date_effective_at()) AND (ro.residence_date < '%'::date) )
AND ro.is_active_manual
-- Does we need this clause?
--AND '2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')
$$;
-- Define UTILITY_PR query
quer_utility_pr := $$
SELECT
ro.client_id,
'%'::date AS payment_date,
'UTILITY_PR'::text AS payment_type_code,
--0- ROUND(rent_amount_tenant / days_in_month(ro.residence_date) * ('%' - ro.residence_date))::decimal(7,2),
0 - ROUND(pro_rate_amount(rent_amount_tenant,ro.residence_date,target_date()-1)) AS amount,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
NULL AS comment,
tenant_vendor_number AS vendor_number
-- FROM residence_own ro
FROM residence_own_current ro
LEFT JOIN rent_info ri ON
(ri.client_id=ro.client_id AND
'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
-- WHERE ro.residence_date BETWEEN ('%'::date - '1 month'::interval)::date AND ('%'::date -1)
-- For Security and prior payments, test whether record was added during this period
WHERE ( (ro.added_at > target_date_effective_at()) AND (ro.residence_date < '%'::date) )
AND ro.is_active_manual
AND rent_amount_tenant < 0
$$;
-- Limited to one client?
cid_clause := COALESCE(' AND ro.client_id =' || mclient::text || ' ','');
-- Don't post already existing payments
duplicate_clause := ' AND NOT ROW(client_id,payment_type_code,payment_date) IN (SELECT client_id,payment_type_code,payment_date FROM payment_valid) ';
union_clause := ' UNION ';
-- Plug the date into all the queries
mdate_text := mdate::text;
quer_assist_x := REPLACE(quer_assist,'%',mdate_text) || cid_clause;
quer_assist_pri_x := REPLACE(quer_assist_pri,'%',mdate_text) || cid_clause;
quer_utility_x := REPLACE(quer_utility,'%',mdate_text) || cid_clause;
quer_utility_pr_x := REPLACE(quer_utility_pr,'%',mdate_text) || cid_clause;
quer_security_x := REPLACE(quer_security,'%',mdate_text) || cid_clause;
final_query := CASE COALESCE(mtype,'ALL')
WHEN 'ASSIST' THEN quer_assist_x
WHEN 'ASSIST_PRI' THEN quer_assist_pri_x
WHEN 'UTILITY' THEN quer_utility_x
WHEN 'SECURITY' THEN quer_security_x
WHEN 'UTILITY_PR' THEN quer_utility_pr_x
WHEN 'ALL' THEN
quer_assist_x || union_clause ||
quer_assist_pri_x || union_clause ||
quer_utility_x || union_clause ||
quer_security_x || union_clause ||
quer_utility_pr_x
END;
--RAISE NOTICE 'Final query %',final_query;
-- Do the query and return the payments
FOR payment IN EXECUTE final_query LOOP
pay_test.payment_id := nextval('tbl_payment_payment_id_seq');
pay_test.client_id := payment.client_id;
pay_test.payment_date := payment.payment_date;
pay_test.payment_type_code := payment.payment_type_code;
pay_test.amount := payment.amount;
pay_test.housing_project_code := payment.housing_project_code;
pay_test.housing_unit_code := payment.housing_unit_code;
pay_test.grant_number_code := payment.grant_number_code;
pay_test.vendor_number := payment.vendor_number;
pay_test.added_by=COALESCE(by_who,sys_user());
pay_test.added_at=current_timestamp;
pay_test.changed_at=current_timestamp;
pay_test.changed_by=COALESCE(by_who,sys_user());
pay_test.sys_log='Payment from generate payments';
pay_test.comment=payment.comment;
pay_test.is_deleted=false;
pay_test.old_is_checkissued=false;
pay_test.old_is_manualentry=false;
pay_test.old_is_duplicate_unit=false;
pay_test.old_is_paidalready=false;
pay_test.old_is_checknotsent=false;
pay_test.is_subsidy=false;
pay_test.is_void=false;
pay_test.target_date=target_date();
-- IF payment IS NOT NULL THEN
RETURN NEXT pay_test;
-- END IF;
END LOOP;
RETURN;
END;
$FUNC$ LANGUAGE PLPGSQL;
/*
CREATE TABLE ptest AS
SELECT
--ro.residence_own_id, -- delete me
ro.client_id,
'2013-11-01'::date AS payment_date,
'ASSIST'::text AS payment_type_code,
rent_amount_spc AS amount,
ro.housing_project_code,
ro.housing_unit_code,
ri.grant_number_code,
sys_user() AS added_by,
sys_user() AS changed_by,
'Make me a good sys_log message' AS sys_log
FROM residence_own ro
LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-11-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-11-01'))
WHERE
true-- ro.housing_unit_code='2248'
AND
'2013-11-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-11-01')
AND ro.is_active_manual
UNION
SELECT
--ro.residence_own_id, -- delete me
ro.client_id,
'2013-11-01'::date AS payment_date,
'UTILITY'::text AS payment_type_code,
0 - rent_amount_tenant AS amount,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
sys_user() AS added_by,
sys_user() AS changed_by,
'Make me a good sys_log message' AS sys_log
FROM residence_own ro
LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-11-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-11-01'))
WHERE
-- ro.housing_unit_code='2248'
'2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')
AND
rent_amount_tenant < 0
--) FOO group by 1,2,3,4,5,6,7 HAVING COUNT(*) > 1
--ORDER BY 3,2
*/
/*
UNION
SELECT
ro.client_id,
'2013-10-01'::date,
'SECURITY'::text,
security_deposit,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
sys_user(),
sys_user(),
'Make me a good sys_log message'
FROM residence_own ro
LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-10-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-10-01'))
WHERE
ro.residence_date BETWEEN '2013-09-01' AND '2013-10-01'
AND ro.is_active_manual
AND '2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')
UNION
SELECT
ro.client_id,
'2013-10-01'::date,
'ASSIST_PRI'::text,
rent_amount_spc / 30 * (30 - LEAST(30,'2013-10-01' - ro.residence_date))::decimal(7,2),
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
sys_user(),
sys_user(),
'Make me a good sys_log message'
FROM residence_own ro
LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-10-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-10-01'))
WHERE
ro.residence_date BETWEEN '2013-09-01' AND '2013-10-01'
AND ro.is_active_manual
AND '2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')
;
*/
/*
create view ptest_mip as
SELECT
p.client_id,
'APISPC' ||
to_char(current_date + '2 month'::interval, 'MMYY') ||
'20' ||
p.grant_number_code as month_grant,
'BP' as c2,
'Imported Session'as c3,
date_part('month',current_date + '2 month'::interval) ||
'/1/' ||
date_part('year',current_date + '2 month'::interval) as payment_date,
'API' as c4,
p.grant_number_code ||
'_' ||
(SELECT ro2.old_access_id
FROM residence_own ro2
WHERE ro2.client_id=p.client_id
ORDER BY ro2.residence_date DESC LIMIT 1) ||--FIXME ri.residence_own_id ||
'_' ||
date_part('year',current_date + '2 month'::interval) ||
'-' ||
date_part('month',current_date + '2 month'::interval) as coded_field,
--E'\"' ||
SUBSTRING(c.name_first from 1 for 1) ||
'. ' ||
SUBSTRING(c.name_last from 1 for 10) ||
', ' ||
(SELECT ri.housing_unit_label
FROM rent_info ri
WHERE ri.client_id=p.client_id
ORDER BY ri.residence_date DESC LIMIT 1) ||
--E'\"' as name_unit,
E'' as name_unit,
(SELECT ri2.vendor_number
FROM rent_info ri2
WHERE ri2.client_id=p.client_id
ORDER BY ri2.residence_date DESC LIMIT 1) as vendor_number,--'Vendor',--FIXME vendor
'N' as a_flag,
'10' as c5,
'7649' as c6,
'200' as c7,
'100' as c8,
'20' ||
p.grant_number_code as grant_no,
'1' as c9,
(p.amount*100)::INT as amount_pennies, --check amount
'0' as c10,
'Check' as check_address
FROM ptest p
--LEFT JOIN rent_info ri ON
--ri.client_id=p.client_id
--JOIN residence_own ro ON
--p.client_id=ro.client_id
LEFT JOIN client c ON
c.client_id=p.client_id;
*/
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
