Re: PL/pgSQL 2

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