Odd Timestamp Error WAS Re: Function Creation Error
От | Joshua Kramer |
---|---|
Тема | Odd Timestamp Error WAS Re: Function Creation Error |
Дата | |
Msg-id | Pine.LNX.4.64.0704080440040.16068@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Function Creation Error (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Odd Timestamp Error WAS Re: Function Creation Error
|
Список | pgsql-novice |
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 > >
В списке pgsql-novice по дате отправления: