quoting and EXECUTE in plpgsql function
От | Matthew Nuzum |
---|---|
Тема | quoting and EXECUTE in plpgsql function |
Дата | |
Msg-id | 004801c283a9$83f023e0$6501a8c0@mattspc обсуждение исходный текст |
Ответы |
Re: quoting and EXECUTE in plpgsql function
|
Список | pgsql-general |
Hello, I'm writing my first large plpgsql function and I'm having a little difficulty. First, let me say that I'm editing my function through phpPgAdmin, and I believe that it requires you to escape all single quotes when entering functions. I'm not sure if that is also true when creating a function using the command line tools. The function will take a raw log entry from apache and break it down to it's parts so that it can be entered into the database. The bulk of the work is processing the log file entry. The final part of the process will insert the data into a table and return the OID. The OID isn't really needed, but I'm returning it in case I want to use it while normalizing in a different step. Here's the exact syntax that doesn't currently work: DECLARE log_data text; pos int2; -- temporary variable vhost text; -- the name of the vhost host text; -- varchar(15) could be used, except that if the ips -- are resolved to names, we''ll have probs. date TIMESTAMPTZ; path text; method text; -- usually GET, POST or HEAD protocol text; -- usually HTTP/1.1 status int2; size int; referer text; user_agent text; oid int; BEGIN -- -- a lot of stuff snipped out -- EXECUTE '' INSERT INTO raw_data '' || '' (vhost,host,date,path,method,protocol,status,size,referrer,user_agent) '' || '' VALUES ('' || quote_ident(vhost) || '', '' ||quote_ident(host) || '', '' || quote_ident(date) || '', '' || quote_ident(path) || '', '' || quote_ident(method) || '', '' || quote_ident(protocol) || '', '' || status || '', '' || size || '', '' || quote_ident(referer) || '', '' || quote_ident(user_agent) || '');''; When I enter a log file entry with a vhost of www.domain.com I get the following error message: ERROR: Attribute 'www.domain.com' not found Maybe I made a mistake by using the same name for the column and the variable, but I suspect that if I get the quoting right, that shouldn't be a problem. Can anyone suggest the proper way to quote it? P.S. Please reply-to-all, I get the digest version. Matthew Nuzum
В списке pgsql-general по дате отправления: