Re: INSERT ... RETURNING in v8.2

On Jun 12, 2007, at 11:40 AM, Vincenzo Romano wrote:

>
> On Tuesday 12 June 2007 16:35:05 Martijn van Oosterhout wrote:
>> On Tue, Jun 12, 2007 at 04:18:32PM +0200, Vincenzo Romano wrote:
>>> Well, at least on v8.2.4 I cannot return count(*), that is the
>>> number of lines actually inserted into the table. Nor I can
>>> return any aggregate function of them.
>>
>> I don't think anybody considered the possibility of using an
>> aggregate there, primary because for an aggregate you need a group
>> by. What has been discussed is nested statements, like:
>>
>> SELECT a, count(*) FROM
>> (INSERT <foo> RETURNING a, b)
>> GROUP BY a;
>>
>> But I don't think that's implemented (the interactions with
>> triggers havn't been worked out I think)
>>
>>> Amk I doing anything wrong or is there some missing sentence in
>>> the documentation?
>>
>> When the docs talk about an "expression" they don't mean
>> aggregates, since they are not functions in the ordinary sense.
>>
>> Hope this helps,
>
> I feel that your remarks make some sense.
>
> First, the documentation says "any expression using the table's
> columns is allowed".
>
> Second, I'm not using nested statements, but rather a plain
> INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
> function body). It should not need any GROUP BY as the query is
> plain.
>
> Maybe the solution is somewhere in between what you say and what I'd
> expect. Of course at the moment I have added an extra SELECT COUNT(*)
> in order to get that number.

Not entirely sure what you're doing but at least with Perl you can
always ask for the number of affected rows: $sth->rows after you run
an INSERT.