Re: plpgsql function errors

Martijn -

Thank you so much for your help. I finally got the stored procedure to
work as I wanted and your advice on nextval() and currval() helped me
get around the expected problem of how to address two transactions
trying to acquire a lock on the same table.

Best Regards,
Juan

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog(at)svana(dot)org]
Sent: Friday, February 04, 2005 4:49 PM
To: Juan Casero (FL FLC)
Cc: Postgresql General
Subject: Re: [GENERAL] plpgsql function errors

On Fri, Feb 04, 2005 at 01:14:44PM -0600, Juan Casero (FL FLC) wrote:
> Here is the output of that command. I ran it in a unix shell and
> redirected the psql output to a file so I haven't touched it...

Well, here's the problem. Your definition is:

> integer | public | trx_id | character varying, smallint,
> character, date, character, character, character, date, character |
> postgres | plpgsql |

And your call is:
> > customer_service=# select
> >
>
trx_id('JUANCASERO3055128218',805,'CREDIT','02/02/05','1','1','Aventura'
> > ,'02/01/05','Tom');
> > ERROR: function trx_id("unknown", integer, "unknown", "unknown",
> > "unknown", "unknown", "unknown", "unknown", "unknown") does not
exist
> > HINT: No function matches the given name and argument types. You
may
> > need to add explicit type casts.

You didn't quote the second argument so PostgreSQL assumes it's an
integer whereas your definition asks for a smallint. So you either need
to quote the number like '805', or cast it like 805::smallint.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is
a
> tool for doing 5% of the work and then sitting around waiting for
someone
> else to do the other 95% so you can sue them.