Re: Function Creation Error

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