Thread: Problems Testing User-Defined Function

Hi list,
I'm a newbie and I have a problem. I've defined the following function using
pgAdmin but I am clueless as to how I can test it. I will eventually be
calling this function from a .NET application but I want to test it using
raw SQL first. Here's the function definition:
CREATE OR REPLACE FUNCTION sp_insert_manifest(_sender varchar(255),           _sender_email varchar(255),
_reply_tovarchar(255),           _filename varchar(255),           _file oid,          _datetime_sent timestamp)
RETURNSinteger AS $$
 
-- blah blah --

$$ LANGUAGE plpgsql;
I am trying to pass the following values to the function but I have been so
far unsuccessful.
SELECT sp_insert_manifest('me', me@you.com', 'me@you.com', 'test.txt', NULL,
'2006/06/09')
Can anyone help me here?
Thanks,
Rommel the iCeMAn.

----- Original Message ----- 
From: "Rommel the iCeMAn" <icecrew@gmail.com>
>
> I am trying to pass the following values to the function but I have been 
> so
> far unsuccessful.

What error message are you receiving?

>
> SELECT sp_insert_manifest('me', me@you.com', 'me@you.com', 'test.txt', 
> NULL,
> '2006/06/09')

Is this a direct paste?  If so, you are missing the apostrophe before me@ 
you.com.

Regards,
George 

You will have to use the "CALLED ON NULL INPUT" option to "create function" (Postgresql 8.1, I don't know about other 
versions) if you expect NULL arguments.

Jim


---------- Original Message -----------
From: "Rommel the iCeMAn" <icecrew@gmail.com>
To: "PostgreSQL SQL Mailing List" <pgsql-sql@postgresql.org>
Sent: Fri, 9 Jun 2006 16:01:26 -0400
Subject: [SQL] Problems Testing User-Defined Function

> Hi list,
> 
> I'm a newbie and I have a problem. I've defined the following function using
> pgAdmin but I am clueless as to how I can test it. I will eventually be
> calling this function from a .NET application but I want to test it using
> raw SQL first. Here's the function definition:
> 
> CREATE OR REPLACE FUNCTION sp_insert_manifest(_sender varchar(255), 
>            _sender_email varchar(255), 
>            _reply_to varchar(255), 
>            _filename varchar(255), 
>            _file oid,
>            _datetime_sent timestamp) RETURNS integer AS $$
> 
> -- blah blah --
> 
> $$ LANGUAGE plpgsql;
> 
> I am trying to pass the following values to the function but I have been so
> far unsuccessful.
> 
> SELECT sp_insert_manifest('me', me@you.com', 'me@you.com', 'test.txt', NULL,
> '2006/06/09')
> 
> Can anyone help me here?
> 
> Thanks,
> Rommel the iCeMAn.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
------- End of Original Message -------

Hi guys,

Thanks for the input so far. No it wasn't a direct paste, what I did in fact
type was SELECT sp_insert_manifest('me', 'me@you.com', 'me@you.com',
'test.txt', NULL, '2006/06/09'). The error message said something like
function sp_insert_manifest(character varying, character varying, character
varying, character varying, "unknown", timestamp) does not exist. It seems
NOT to like my NULL parameter.

I'll investigate the "CALLED ON NULL INPUT" option that Jim suggested.

Thanks again,
Rommel Edwards.

"Rommel the iCeMAn" <icecrew@gmail.com> writes:
> Thanks for the input so far. No it wasn't a direct paste, what I did in fact
> type was SELECT sp_insert_manifest('me', 'me@you.com', 'me@you.com',
> 'test.txt', NULL, '2006/06/09'). The error message said something like
> function sp_insert_manifest(character varying, character varying, character
> varying, character varying, "unknown", timestamp) does not exist.

You were asked for the exact error message, not an approximation.  Given
that input I'd have expected all the arguments to show as "unknown" in
the error message, because none of them have well-defined types.

> I'll investigate the "CALLED ON NULL INPUT" option that Jim suggested.

Since that's the default anyway, adding it won't help you.  I suspect
you need to be looking for more prosaic issues, like whether the function
name is spelled correctly.
        regards, tom lane