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 or 10 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