Hi,
over the last couple of days I have been asking about procedures in postgres so I can write a stored procedure for returning data for reports. I started today and thought I would just try and run my procedure to see if I've got any mistakes so far. I'm writing the procedure in notepad so I can run it as a script via command prompt. Here's my code:
create or replace function reports (report_id integer, adviser_id integer,provider_id integer,
introducer_id integer, plangroup_id integer, plantype_id integer, datespecific_start date,
datespecific_end date,child24 date,child26 date) returns setof record as'
begin
--PandC report
if report_id=18 then
select
tblemployee.employee_first_name,tblemployee.employee_surname,tblnewbusiness.newbusiness_date_issued,
tblclient.client_first_name,tblclient.client_middle_names,tblclient.client_surname ,tblplantypes.plantype_group,
tblplangroups.plan_group,tblproviders.provider_company, tblnewbusiness.newbusiness_policy_number,
tblnewbusiness_sum_assured,tblnewbusiness.newbusiness_benefit, tblnewbusiness.newbusiness_premium ,
tblnewbusiness.brokerage,tblnewbusiness.newbusiness_comments
from
(tblproviders right join tblnewbusiness on (tblproviders.provider_ref=tblnewbusiness.provider_ref);
endif;
end;
'language plpgsql;
When I tried running the script using psql db -f reports_sp.txt I get these errors:
psql:reports_sp.txt: 22: ERROR: syntax error at the end of input at charecter 583
QUERY: SELECT tblemployee.employee_first_name,tblemployee.employee_surname,tblnewbusiness.newbusiness_date_issued,
tblclient.client_first_name,tblclient.client_middle_names,tblclient.client_surname,tblplantypes.plantype_group ,
tblplangroups.plan_group,tblproviders.provider_company, tblnewbusiness.newbusiness_policy_number,
tblnewbusiness_sum_assured,tblnewbusiness.newbusiness_benefit, tblnewbusiness.newbusiness_premium,
tblnewbusiness.brokerage ,tblnewbusiness.newbusiness_comments
from
(tblproviders right join tblnewbusiness on (tblproviders.provider_ref=tblnewbusiness.provider_ref)
CONTEXT: SQL statement in PL/PgSQL function "reports" near line 14
psql:reports_sp.txt:22: LINE 1: ...s on (tblproviders.provider_ref=tblnewbusiness.provider_ref)
psql:reports_sp.txt:22:
Can anyone point me in the right direction on what I need to do to make it work?
Cheers,
Ben
