Error when creating function in psotgresql 8.1

From: "ben sewell" <mosherben(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Error when creating function in psotgresql 8.1
Date: 2006-08-17 09:16:50
Message-ID: bf6c74d80608170216u4474bdb6mfc1b7334f807aba9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2006-08-17 10:31:39 Re: Error when creating function in psotgresql 8.1
Previous Message Michael Meskes 2006-08-17 07:18:37 Re: [NOVICE] DB insert Error