Thread: to_date syntax error

Hi guys,
I'm nearly at the end of my postgres migration apart from from this
small problem so I am hoping somebody can spot what the problem is.

For starters, I am inputing all the parameters as varchar chars and
some of the parameters are optional and Access doesn't like passing
null integers to postgres.

Here's my code:

create or replace function reports (inreport_id varchar,inadviser_id
varchar,inprovider_id varchar,inintroducer_id varchar,
inplangroup_id varchar,inplantype_id varchar,indatespecific_start
varchar,indatespecific_end varchar,inchild24 varchar,inchild26
varchar)
returns record as'

declare myrec record;
declare ireport_id integer;
declare iadviser_id integer;
declare iprovider_id integer;
declare iintroducer_id integer;
declare iplangroup integer;
declare iplantype integer;
declare idatespecific_start date;
declare idatespecific_end date;
declare ichild24 date;
declare ichild26 date;

begin

ireport_d=CAST(inreport_id as integer);
iadviser_id=CAST(inadviser_id as integer);
iprovider_id=CAST(inprovider_id as integer);
iintroducer_id=CAST(iintroducer_id as integer);
iplangroup=CAST(inplangroup as integer);
iplantype=CAST(inplangroup_id as integer);
idate_start=to_date(indatespecific_start,'DD-MM-YYYY');
idate_end=to_date(indatespecific_end,'DD-MM-YYYY');
ichild24=to_date('inchild24','DD-MM-YYYY');
ichild26=to_date('inchild26','DD-MM-YYYY');

I'll be using the variables later in the function so any advice/help
is appreciated.

Cheers,
Ben

On Tue, Aug 29, 2006 at 10:08:33AM +0100, ben sewell wrote:
> I'm nearly at the end of my postgres migration apart from from this
> small problem so I am hoping somebody can spot what the problem is.

You didn't describe the problem symptoms were but I see a few trouble
spots:

1. Check your variable names.  There are several mismatches between
what's declared and what's used (e.g., casting inplangroup instead
of inplangroup_id, assigning ireport_d instead of ireport_id, and
assigning idate_start and idate_end instead of idatespecific_start
and idatespecific_end).

2. If you use single quotes to quote the function body then you'll
need to escape single quotes within the body as '' or \'.  If you're
using PostgreSQL 8.0 or later then consider using dollar quotes to
quote the function body.

3. Two of the to_date calls quote the first argument ('inchild24'
and 'inchild26') when they shouldn't.

Also, you don't need to repeat "declare" for each declaration.  That
does appear to work but you can write "declare" just once, like this:

declare
  myrec record;
  ireport_id integer;
  iadviser_id integer;
  ...
begin
  ...

--
Michael Fuhr