Re: Error when creating function in psotgresql 8.1

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