Обсуждение: Function Creation Error

Hello all,

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;
ALTER FUNCTION "fnGenerateAuthTicket"(character varying, integer, integer,
inet) OWNER TO prod_user;

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.

Also, is my use of interval correct?  I want to have a parameter with
number of hours to expire, but I'm not sure how to make sure that the
'p_expire_hours hours' is interpreted as, say, '2 hours' instead of the
literal itself 'p_expire_hours hours'.

Thanks,
-Josh

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

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
>
>

Joshua Kramer <josh@globalherald.net> writes:
> 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'"

You seem to be confused about the difference between a literal constant
and an expression.  That should just be

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')

            regards, tom lane

> You seem to be confused about the difference between a literal constant
> and an expression.  That should just be

Thanks again, Tom.  I didn't notice that - I'll have to see if I can coax
the software into using the expression instead of the string literal.  (It
may be an ADODB thing - because the web app is running a parameterized
query, it automatically inserts the quotation marks.  I'll need to
determine how to get around that.)

Cheers,
-J

Joshua Kramer <josh@globalherald.net> writes:
>> You seem to be confused about the difference between a literal constant
>> and an expression.  That should just be

> Thanks again, Tom.  I didn't notice that - I'll have to see if I can coax
> the software into using the expression instead of the string literal.  (It
> may be an ADODB thing - because the web app is running a parameterized
> query, it automatically inserts the quotation marks.  I'll need to
> determine how to get around that.)

Hm, you might have some difficulty there --- I wouldn't be surprised if
ADODB thinks it's protecting you from SQL-injection attacks.  You should
not expect that you can insert SQL expressions via parameter
substitution mechanisms.  You might find that computing the timestamps
on the client side is the path of least resistance...

            regards, tom lane