Odd Timestamp Error WAS Re: Function Creation Error

Thanks, Tom.  Now I've spent many hours fighting over a really odd error
message.  In my Postgres 8.2.3 (CentOS 4.4, RPMS packaged by PGDG) log, I
see this error:

LOG:  statement: INSERT INTO auth_tickets (user_id,
expire_datetime, init_datetime, init_ip_addr, ticket_serial) VALUES ('2',
'now() + 6 * interval ''1 hour''', 'now()', '192.168.2.2',
'44d6e7d4b2e87632a65cd34501aeea614bffde5f')
ERROR:  invalid input syntax for type timestamp: "now() + 6 * interval '1
hour'"

Basically, it does not like it when I insert this string into a timestamp
field:

now() + 6 * interval '1 hour'

But, check this out.  I can do the following, with no errors, and storing
valid data in the table:

create table test ( field1 timestamp, field2 varchar(50));
insert into test values (cast(now() + 6 * interval '1 hour' AS timestamp),
"Test 1");
insert into test values (now() + 6 * interval '1 hour', 'Test 2');

I've also tried this string with the same results on both tables:

now() + interval '6 hour'

The only difference is this: the auth_tickets table used to have the
expire_datetime as a "timestamp without timezone" but I modified the field
to simply be "timestamp".  Also, with auth_tickets I'm inserting from an
ADODB-based PHP application, and with the test table I'm using psql
command line client.

Any ideas?

Cheers,
-J


On Sat, 7 Apr 2007, Tom Lane wrote:

> Joshua Kramer <josh@globalherald.net> writes:
>> I have a function definition built with pgadmin3 that looks like this:
>
>> CREATE FUNCTION "fnGenerateAuthTicket"(p_ticket_serial character varying,
>> p_expire_hours integer, p_user_id integer, p_ip_addr inet) RETURNS
>> character varying AS
>> $BODY$
>> insert into auth_tickets(user_id, expire_datetime, init_datetime,
>> init_ip_addr, ticket_serial)
>>          values (p_user_id, now() + interval 'p_expire_hours
>> hours', now(), p_ip_addr, p_ticket_serial);
>> $BODY$
>> LANGUAGE 'sql' VOLATILE;
>
>> pgadmin3 gives me an error, "Error: column p_user_id does not exist at
>> character 280".  The function examples I've seen show that you use the
>> variable parameter names just like normal fields, and that's what I'm
>> doing here.  I'm not sure where the error comes from.
>
> The SQL function language doesn't (yet) know how to reference parameters
> by name --- you'd need to write $1 for p_ticket_serial etc.  Or use
> plpgsql, which does know about parameter names.
>
>> Also, is my use of interval correct?
>
> No, as you suspected, it isn't.  The best way to do this is to use
> number-times-interval multiplication:
>
>     $2 * interval '1 hour'
>
>             regards, tom lane
>
>