Re: DO ... RETURNING

2013/6/11 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
> Hi,
>
> That topic apparently raises each year and rehash the same points.
>
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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