Re: PL/pgSQL 2

On Sep 1, 2014, at 10:24 PM, Craig Ringer <craig(at)2ndQuadrant(dot)com> wrote:

> On 09/02/2014 08:09 AM, Neil Tiffin wrote:
>> Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL.
>
> PL/Perl is plenty fast, FWIW.
>

Good to know. I used to do a lot of perl and will revisit the language.

> I agree that it is unfortunate that we don't have an in-core trusted
> "real language" PL other than PL/Perl. I am personally hoping that PL/V8
> will be in a position to be adopted as "PL/JavaScript" soon, as that
> would be an excellent fit with how the language fashion world is
> currently moving - JSON and JavaScript abound.
>
> More seriously, JavaScript is also a good fit for a trusted PL. I've
> long favoured Lua because of the excellent embeddable runtime and
> security-friendly design, but it's never really got the uptake required
> to make it a serious contender.
>
> I'd be quite happy to see PL/JavaScript in-core.
>
> (The other obvious candidate would be PL/Ruby, but it doesn't have an
> untrusted variant, and AFAIK Ruby is no better than Python when it comes
> to supporting a secure runtime: hopeless.)
>
>> That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language.
>
> I couldn't disagree more.
>
> If we were to implement anything, it'd be PL/PSM
> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
> quirky as anything else the SQL committee has brought forth, but it's at
> least a standard(ish) language.

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’;

Wow, so after I wrote the above, I went back to review the docs and lo and behold the format function was added in 9.1 (I think).

It turns out it can already be written as (not tested)
EXECUTE format( ‘TRUNCATE TABLE %I’, mviews.mv_name);
EXECUTE format( ‘INSERT INTO %I %L’, mviews.mv_name, mviews.mv_query);

That’s not so bad and very similar to how it would have to be done in many other languages. However the first three examples in the docs for PL/pgSQL for dynamic queries and many, many other places don’t show this approach. And the format syntax is only listed 4 lines from the bottom of the section as a ‘you can also do this’. From the position and wording I would interpret that something must be wrong with using the format function to construct dynamic queries, but, who knew, I never scrolled down that far in the docs. Thank you to whomever added the format() function.

So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended? And the format function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks)

Neil