Error when creating function in psotgresql 8.1

Поиск
Список
Период
Сортировка
От ben sewell
Тема Error when creating function in psotgresql 8.1
Дата
Msg-id bf6c74d80608170216u4474bdb6mfc1b7334f807aba9@mail.gmail.com
обсуждение исходный текст
Ответы Re: Error when creating function in psotgresql 8.1
Список pgsql-novice
Дерево обсуждения
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

В списке pgsql-novice по дате отправления: