Postgres Pro
Downloads
Home   >   mailing lists

Thread: quoting and EXECUTE in plpgsql function

quoting and EXECUTE in plpgsql function

From
"Matthew Nuzum"
Date:
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

Re: quoting and EXECUTE in plpgsql function

From
Doug McNaught
Date:
"Matthew Nuzum" <cobalt@bearfruit.org> writes:

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

Yes.

> EXECUTE '' INSERT INTO raw_data '' ||

Any reason you're using EXECUTE here?  EXECUTE is really only needed
when the table or column names need to be determined at runtime.  Your
query looks quite normal, so you don't need to use EXECUTE.  As a
bonus, the query will get compiled and the plan saved so you won't be
calling the planner for every row you insert.

Second, I don't think you need quote_ident, especially if you're not
using EXECUTE.

You should just be able to do something like:

INSERT INTO foo_table (bar, baz, quux) VALUES (bar, baz, quux);

(assuming bar, baz and quux are aliased in your declaration section.

Give that a try.

-Doug

Re: quoting and EXECUTE in plpgsql function

From
Tom Lane
Date:
"Matthew Nuzum" <cobalt@bearfruit.org> writes:
> 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) ||
> '');'';

I believe you want quote_literal, not quote_ident, in all these cases.
quote_ident would be used if you want to use the contents of the plpgsql
variable as a *name* in the resulting SQL statement; but you want to use
it as a literal constant, AFAICT.

But actually, this seems like the most painful and least efficient way
you could choose to do it.  Do you really need an EXECUTE?  I'd be
inclined to write just

INSERT INTO raw_data
(vhost,host,date,path,method,protocol,status,size,referrer,user_agent)
VALUES (vhost_parm, host_parm, ...);

Note that when doing it that way, you *must* choose plpgsql variable
names that don't conflict with the table column names; plpgsql is not
smart enough to understand that in
    INSERT INTO raw_data (vhost, ...) VALUES(vhost, ...);
you'd like it to substitute for the second "vhost" and not the first.

            regards, tom lane