From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Joshua Kramer <josh(at)globalherald(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Function Creation Error |
Date: | 2007-04-07 04:53:51 |
Message-ID: | 2164.1175921631@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Joshua Kramer <josh(at)globalherald(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Kramer | 2007-04-08 08:58:53 | Odd Timestamp Error WAS Re: Function Creation Error |
Previous Message | Joshua Kramer | 2007-04-07 04:39:44 | Function Creation Error |