Re: quoting and EXECUTE in plpgsql function

"Matthew Nuzum" <cobalt(at)bearfruit(dot)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