On 09/02/2014 11:10 PM, Neil Tiffin wrote:
> I’d be happy with PL/Javascript, PL/Lua or ?? as long as creating dynamic SQL queries was simple, i.e. no goofball 6
or10 level quotes to make it work. So instead of (from the docs, 40.6.4. Looping Through Query Results)
>
> EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
> EXECUTE 'INSERT INTO '
> || quote_ident(mviews.mv_name) || ' '
> || mviews.mv_query;
>
> should be something like:
> EXECUTE ‘TRUNCATE TABLE $$mviews.mv_name’;
> EXECUTE ‘INSERT INTO $$mviews.mv_name $$mviews.mv_query’;
I think we need to remove those sections entirely from the docs, in
favour of using only format(...) with EXECUTE ... USING .
Too many people seem to see that, and not format(...).
> So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended?
Historical, really.
> And the format function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks)
That's a real oversight that needs fixing. Thanks.
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services