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
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 |