Re: plpgsql function errors

I tried putting those values into strings like you describe below but
then the server bombs.  e.g...

customer_service=# select
trx_id('JUANCASERO3055128218','CREDIT','02/02/05','1','1','Aventura','02
/01/05','Tom');
ERROR:  function trx_id("unknown", "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.
customer_service=#


It doesn't recongnize the paramter types and therefore can't match it to
any known function signature.  Even when I explicitly typcast the
parameters like so...

customer_service=# select
trx_id(JUANCASERO3055128218::char,CREDIT::char,02/02/05::date,1::char,1:
:char,Aventura::char,02/01/05::date,Tom::char);
ERROR:  column "juancasero3055128218" does not exist
customer_service=#

The problem persists.


I like the idea of nextval() and currval().  I was hoping to optimize
the code later.  For now I just wanted to test the concept.  I wanted to
start with the simplest case possible to improve my chances of success
with the procedure and optimize it later.  Any ideas on why the function
call fails?

Thanks,
Juan
-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Friday, February 04, 2005 1:06 PM
To: Juan Casero (FL FLC)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql function errors

On Fri, Feb 04, 2005 at 11:40:50AM -0600, Juan Casero (FL FLC) wrote:
> Hi Everyone -
>
> I am new to this list and although I have been using postgresql on and

> off for about a year now.  I am trying to develop a webapp using perl
> and cgi with postgresql 7.4.6 as a backend database.  One of the
> things I need is to create a transactions table that will record a row

> of data and then return the transaction id to the caller.  I have
> decided to implement this piece of the webapp as a stored procedure in
pl/pgsql.
> The following is the source code...

Firstly, I think you should look up the documentation for nextval(),
currval(). There is no possiblity of returning the wrong value even if
another transaction is running concurrently. And you don't need to lock
the table, which is good for performance.

<snip>

> # select
> trx_id(JUANCASERO3055128218,CREDIT,02/02/05,1,1,Aventura,02/01/05,Tom)
> ;
>
> I get the following error message
>
> ERROR:  column "juancasero3055128218" does not exist

You need to put those values into strings. Unless you do that it thinks
you're referring to a column named that. Try:

select
trx_id('JUANCASERO3055128218','CREDIT','02/02/05',1,1,'Aventura','02/01/
05','Tom');

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.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.