Thread: Error when creating function in psotgresql 8.1

On 8/17/06 5:16 AM, "ben sewell" <mosherben@gmail.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

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

On 8/17/06 8:42 AM, "ben sewell" <mosherben@gmail.com> wrote:

> 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. :)

Ben,

Great to hear you got it working.  Postgresql is pretty cool, eh?

Sean

On 8/17/06 9:13 AM, "ben sewell" <mosherben@gmail.com> wrote:

> Hi Sean,
> yup it is. I wasn't expecting things to be smooth even if I did know oracle
> for the migration. It's handy that there is alot of support if you get stuck
> on using it.
>
> One follow up question: as I mentioned before, the procedure is to process
> MS Access reports because of how slow JET takes to generate them (some
> tables have 7k records). How would I create the report once I've finished
> the procedure? I suppose its going to be a VBA coding job after I provide
> the execute reports([parameters]) for the View Report button but would like
> to know exactly what I need to do after the procedure is done.

I don't use Access, so I really can't answer that.  You can try posting the
question again when you get a bit closer to needing that functionality.

Sorry.

Sean

> > tables have 7k records). How would I create the report once I've finished
> > the procedure? I suppose its going to be a VBA coding job after I provide
> > the execute reports([parameters]) for the View Report button but would like
> > to know exactly what I need to do after the procedure is done.
>
> I don't use Access, so I really can't answer that.  You can try posting the
> question again when you get a bit closer to needing that functionality.

I can fill in a little hear.  I've kept a book mark on this link for future reference.
http://archives.postgresql.org/pgsql-general/2005-05/msg01260.php

There are others on the web that do the same thing.
http://support.microsoft.com/?kbid=232493

Regards,

Richard Broersma Jr.

On 8/17/06 10:48 AM, "ben sewell" <mosherben@gmail.com> wrote:

> Hi Sean,
> that's fine I'll google it later the time when I need it.
>
> Sorry but I got another question about my procedure, I have my parameters
> coming from the access forms which can be left empty (aka null).
>
> I'll need to create handling for when the parameters are null so this would
> be using if statements. Once I've handled the input parameters the select
> into queries will be run. Here's the pseudocode code for it so I was
> wondering if you could tell me the equilivent for it in postgres.
>
> if adviser_id=null then
>   all records are returned irrespective if the adviser_id
> else
>   find records which the adviser_id matches
> endif;

You may want to do something along the lines of:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGS
QL-STATEMENTS-EXECUTING-DYN

Building the query a piece at a time and then executing.  Alternatively, you
can also create multiple functions with different prototypes (different
parameter types)  so that you could have a function like:

My_func(int,int) ....

And

My_func(int)

The two functions, although they have the same name, are different and can
do different things.

Sean