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@mail.nih.gov> wrote:
On 8/17/06 8:12 AM, "ben sewell" <mosherben@gmail.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
