Re: DO ... RETURNING

On 06/11/2013 05:27 PM, Merlin Moncure wrote:
> On Tue, Jun 11, 2013 at 9:45 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> * Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
>>> I agree with all your comments pretty much down the line. Need top
>>> level CALL that supports parameterization and multiple sets that
>>> utilizes background worker (we have example spi worker that gives some
>>> hints about how pl/pgsql could be made to work). Because it's top
>>> level (can't even be inlined to CTE), we can access behaviors that are
>>> not possible in current pl/pgsql, for example setting transaction
>>> isolation in advance of snapshot and changing database connection
>>> mid-procedure.
>> And this still has next-to-nothing to do with the specific proposal that
>> was put forward.
>
> It's a complete feature but completely relevant to the discussion --
> the behaviors have a lot of overlap and CALL is in the standard
> whereas the ad hoc feature DO isn't.
Could you point to the ISO/ANSI SQL CALL definition ?
> This comes up in other feature requests too, like psql bash-like scripting features.
Client side scripting is distinct from kind-of-server-side multi
database scripting.

As I said before, it is doable now using pl/pgsql and dblink / plproxy
and is trivial in pl/python.
> That said, it would be pretty cool if you could inline DO into a CTE
> or more generally into a query (is that possible?)
Currently not possible, especially because DO does not return
anything at this moment.

Again, you can of course run a DO query from any pl (excelp maybe SQL
and pl/pgsql)

hannu=# DO language plpythonu $$
plpy.execute("DO LANGUAGE plpgsql $x$ BEGIN RAISE NOTICE 'hi!'; END;$x$")
$$;
NOTICE: hi!
CONTEXT: SQL statement "DO LANGUAGE plpgsql $x$ BEGIN RAISE NOTICE
'hi!'; END;$x$"
PL/Python anonymous code block
DO

> -- then you'd have something distinct.
I like the CTE approach better as general "inline in query" approach and
DO ... RETURNING as a way for ad-hoc procs or direct remoting from client.

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ