Re: Error when creating function in psotgresql 8.1

Hi Sean,
after some debugging of the error messages I managed to create the function
without any errors. For the record type being returned, it is dependent on
the report_id so I'll have to add the user defined type in the front end
where I execute the procedure.

Anyways, I've attached the working script for anyone who is curious to see
what had to be sorted out. Now I just need to finish off the select
statement in the first if and then finish it for the other 4 if's. :)

Regards,
Ben

On 8/17/06, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> wrote:
>
>
>
>
> On 8/17/06 8:12 AM, "ben sewell" <mosherben(at)gmail(dot)com> wrote:
>
> > 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?
>
> Ben,
>
> You'll have to post the function definition.
>
> Sean
>
>