Re: Error when creating function in psotgresql 8.1

Hi Sean, thanks for spotting the SQL syntax error.

I had a look at another tutorial http://www.faqs.org/docs/ppbook/c19610.htm for
more specific information to try have a go at sorting out my procedure. I've
done changes after looking at it but now I'm getting a syntax error when I'm
declaring a record variable which will return the data. I've attached the
revised version if you want to have a look.

Here's the list of errors when I execute the script:

psql:reports_sp.txt:4: ERROR: syntax error at or near "declare" at
charecter 270
psql:reports_sp.txt:4: LINE 4: declare myrec record;
psql:reports_sp.txt:4: ^
psql:reports_sp.txt:18: ERROR : syntax error at or near "if" at charecter 13
psql:reports_sp.txt:18: LINE 4: if report_id=18 then
psql:reports_sp.txt:18: ^
psql:reports_sp.txt:19: ERROR: syntax error at or near "return" at
charecter 13
psql:reports_sp.txt:19: LINE 1: return myrec;
psql:reports_sp.txt:19: ^
psql:reports_sp.txt:25: ERROR: syntax error at or near "else" at charecter
1
psql:reports_sp.txt:25: LINE 1: else if reports_id=23 then
psql:reports_sp.txt:25: ^
psql:reports_sp.txt:29: ERROR: syntax error at or near "else" at charecter
1
psql:reports_sp.txt:29: LINE 1: else if reports_id=25 then
psql:reports_sp.txt:29: ^
psql:reports_sp.txt:34: ERROR: syntax error at or near "else" at charecter
1 psql:reports_sp.txt:34: LINE 1: else if reports_id=25 then
psql:reports_sp.txt:34: ^
psql:reports_sp.txt:36: ERROR: syntax error at or near "return" at
charecter 1 psql:reports_sp.txt:36: LINE 1: return myrec
psql:reports_sp.txt:36 ^
psql:reports_sp.txt:37: WARNING: there is no transaction in progress
COMMIT
psql:reports_sp.txt:39: ERROR: syntax error at or near "language" at
charecter 1 psql:reports_sp.txt:39: LINE 1: language plpgsql;
psql:reports_sp.txt:39: ^

any ideas?

Cheers,
Ben

On 8/17/06, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> wrote:
>
>
>
>
> On 8/17/06 5:16 AM, "ben sewell" <mosherben(at)gmail(dot)com> wrote:
>
> > 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)
>
> Ben,
>
> When creating functions, I would suggest the following:
>
> 1) Make sure that any SQL that you put into a function does what it is
> supposed to do without errors. Your SQL statement has a syntax error.
> There are mismatched parentheses, and you don't need parens around table
> names.
>
> 2) Start with a simple function from the documentation or a tutorial (see
> below) that you know works and extend that to your needs. You have no
> "return" statement in your function, so unless you want it to return
> nothing, it isn't going to do what you like.
>
> Sean
>
> Tutorial (google for pl/pgsql tutorial):
> http://www.onlamp.com/pub/a/onlamp/2006/05/11/postgresql-plpgsql.html
>
>