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