From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> |
Cc: | Hannu Krosing <hannu(at)2ndquadrant(dot)com>, David Fetter <david(at)fetter(dot)org>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: DO ... RETURNING |
Date: | 2013-06-11 09:30:01 |
Message-ID: | CAFj8pRBqEHqu79mMGhi7jb57JLVtmVg8CjUd0XRnN4QCy-ty+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri Fontaine | 2013-06-11 09:50:50 | Re: DO ... RETURNING |
Previous Message | Dimitri Fontaine | 2013-06-11 09:16:46 | Re: DO ... RETURNING |