Re: DO ... RETURNING

2013/6/11 Dimitri Fontaine <dimitri@2ndquadrant.fr>:
> Hi,
>
> That topic apparently raises each year and rehash the same points.
>
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> probably we can allow using DO in CTE without impact on other SQL
>> statements, and for this purpose we need to know returned
>> TupleDescriptor early.
>
> I still think that DO being a utility statement, having it take
> parameters and return data is going to be a wart in a part of the system
> that has only too many of them already.
>
> My thinking revolves around CTE support for functions:
>
>   WITH FUNCTION name(param, ...)
>         RETURNS type
>        LANGUAGE plpgsql AS (
>     $$ function body here $$
>   )
>   SELECT name(x, ...) FROM ...;
>
>> so I am able accept it, although I am thinking so we are going in
>> strange direction. We are not able do simply tasks simply (we cannot
>> execute SQL script on server side simply) :(. But it is not problem of
>> Hannu design.
>
> With the DO utility command you can already execute SQL script on the
> server quite simply. After all your proposals it's still unclear to me
> where you want to process which data? (I admit this time I didn't pay
> much attention, sorry about that)

there are a significant limit - you cannot "simply" change a database
when you collect statistics over databases, you cannot drop database
...

you cannot return multiple returns sets - show info about tables,
schemas, indexes in one call

what I would

DO
$$
BEGIN FOR r IN pg_databases LOOP   CONNECT r.dbname;   FOR table_name IN SELECT * FROM pg_class ... WHERE owner = 'GD'
AND table_name LIKE 'xx%'   LOOP    IF pg_relsize(table_name) > xxx AND ... THEN      -- show info about dropped table
 SELECT xx FROM pg_class, pg_attribute .... --- SHOW STRUCTURE OF
 
ANY INTERESTING TABLE -- multirecordset output     EXECUTE FORMAT('DROP TABLE %I', table_name); ...

Regards

Pavel


>
>> other question - can we find some readable and intuitive syntax for DO
>> parametrization?
>
> See above.
>
> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support