Thread: PostgresQL equivalent of NOCOUNT

PostgresQL equivalent of NOCOUNT

From

Jochem van Dieten

Date:

14 August 2001, 13:36:46

Does PostgresQL have some way to make update, insert and delete queries
not return the number of affected rows? I know that in MS SQL one would
use NOCOUNT for that.

TIA,

Jochem

Re: PostgresQL equivalent of NOCOUNT

From

Martijn van Oosterhout

Date:

14 August 2001, 15:10:13

On Tue, Aug 14, 2001 at 03:38:08PM +0200, Jochem van Dieten wrote:
> Does PostgresQL have some way to make update, insert and delete queries
> not return the number of affected rows? I know that in MS SQL one would
> use NOCOUNT for that.

Just ignore the result. Postgres has to find all the rows anyway, so
avoiding counting them is hardly going to save any time.

Am I missing something?

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.

Re: PostgresQL equivalent of NOCOUNT

From

Jochem van Dieten

Date:

14 August 2001, 18:20:14

Tom Lane wrote:

> Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
>
>>Does PostgresQL have some way to make update, insert and delete queries
>>not return the number of affected rows? I know that in MS SQL one would
>>use NOCOUNT for that.
>>
>
> Uh ... why?  Seems like a useless anti-feature.  Certainly suppressing
> the count wouldn't save a noticeable number of cycles.

I am not in it for the cycles, just for the laziness ;)
Currently working with a ColdFusion frontend through ODBC, and
ColdFusion is unable to return 2 resultsets for one call to cfquery (the
ColdFusion query implementation). In MS SQL I would use the query below
to suppress one resultset and return the primary key of the recently
inserted record.

SET NOCOUNT ON
  INSERT INTO ()
  VALUES ()
  SELECT @@IDENTITY AS 'Identity'
SET NOCOUNT OFF

I was wondering if something like that is possible in PostgresQL. I know
I can wrap it in a transaction and do a second query or build a
procedure to do it, but this would be much easier (presuming I can use
curval('primary_key_seq') instead of @@identity).

Any suggestions?

Jochem

Re: PostgresQL equivalent of NOCOUNT

From

Tom Lane

Date:

14 August 2001, 18:35:18

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> Does PostgresQL have some way to make update, insert and delete queries
> not return the number of affected rows? I know that in MS SQL one would
> use NOCOUNT for that.

Uh ... why?  Seems like a useless anti-feature.  Certainly suppressing
the count wouldn't save a noticeable number of cycles.

            regards, tom lane

Re: PostgresQL equivalent of NOCOUNT

From

Tom Lane

Date:

14 August 2001, 18:51:29

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
>> Uh ... why?  Seems like a useless anti-feature.  Certainly suppressing
>> the count wouldn't save a noticeable number of cycles.

> I am not in it for the cycles, just for the laziness ;)
> Currently working with a ColdFusion frontend through ODBC, and
> ColdFusion is unable to return 2 resultsets for one call to cfquery (the
> ColdFusion query implementation).

Oh.  So your problem is not that you don't want to know the number of
rows, it's that you don't want any result indication at all for the
INSERT query.

In libpq you could submit two queries as a single query string

    PQexec("INSERT ... ; SELECT ...");

and PQexec would throw away the INSERT result indicator and only return
the SELECT result.  I am not sure if ODBC works similarly, but you could
discuss that with the ODBC guys.

In any case, I see inadequate reason here to justify breaking the FE/BE
protocol (one response per query), which is what it would take to do
what you're asking from the backend side.  Even if we did, it's not at
all clear that that would make ColdFusion work the way you're hoping.

            regards, tom lane

Re: PostgresQL equivalent of NOCOUNT

From

Dwayne Miller

Date:

14 August 2001, 21:10:14

In doing some testing to see if PostgreSQL is a potential backend
database for our ColdFusion application,  I did the following...

created a table named dual (like the Oracle table)  that has one field
and one record...

create table dual (int4 dummy);
insert into dual (dummy) values (1);

// Should probably revoke all insert, update and deletes on dual from
everyone as this solution requires it to contain only one row.

Now from ColdFusion, I can select a sequence from dual and use that in
inserts and updates...

SELECT nextval('mysequence') AS PKEY FROM DUAL;
...
Your inserts and updates using #queryname.pkey#


One solution of many

Dwayne

Jochem van Dieten wrote:

> Tom Lane wrote:
>
>> Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
>>
>>> Does PostgresQL have some way to make update, insert and delete
>>> queries not return the number of affected rows? I know that in MS
>>> SQL one would use NOCOUNT for that.
>>>
>>
>> Uh ... why?  Seems like a useless anti-feature.  Certainly suppressing
>> the count wouldn't save a noticeable number of cycles.
>
>
>
> I am not in it for the cycles, just for the laziness ;)
> Currently working with a ColdFusion frontend through ODBC, and
> ColdFusion is unable to return 2 resultsets for one call to cfquery
> (the ColdFusion query implementation). In MS SQL I would use the query
> below to suppress one resultset and return the primary key of the
> recently inserted record.
>
> SET NOCOUNT ON
>  INSERT INTO ()
>  VALUES ()
>  SELECT @@IDENTITY AS 'Identity'
> SET NOCOUNT OFF
>
> I was wondering if something like that is possible in PostgresQL. I
> know I can wrap it in a transaction and do a second query or build a
> procedure to do it, but this would be much easier (presuming I can use
> curval('primary_key_seq') instead of @@identity).
>
> Any suggestions?
>
> Jochem
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: PostgresQL equivalent of NOCOUNT

From

Jochem van Dieten

Date:

14 August 2001, 22:10:02

Dwayne Miller wrote:
>
> SELECT nextval('mysequence') AS PKEY FROM DUAL;
> ...
> Your inserts and updates using #queryname.pkey#

I know, but it has 2 queries again, which is exactly the reason why I
don't want it (I am actually developing this to be used by others).
BTW, you can just call functions without a FROM, as in:
SELECT now() AS test

> One solution of many

I think I will just replace cfquery with cf_query. Then have cf_query
replace every insert with a function call (so I don't have to double all
the quotes manually), where the function takes care about the rest and
returns #insertedrecord.id# or whatever. Would probably be the easiest
way to explain to others.

Jochem

Re: PostgresQL equivalent of NOCOUNT

From

"Dwayne Miller"

Date:

14 August 2001, 23:16:39


Jochem van Dieten wrote:

> Dwayne Miller wrote:
>
>>
>> SELECT nextval('mysequence') AS PKEY FROM DUAL;
>> ...
>> Your inserts and updates using #queryname.pkey#
>
>
> I know, but it has 2 queries again, which is exactly the reason why I
> don't want it (I am actually developing this to be used by others).
> BTW, you can just call functions without a FROM, as in:
> SELECT now() AS test

Not very portable... which was a requirement for me.

I generally have to use the same key in a series of 1 to 3 inserts, so
this solution seems to work well.

>
>> One solution of many
>
>
> I think I will just replace cfquery with cf_query. Then have cf_query
> replace every insert with a function call (so I don't have to double
> all the quotes manually), where the function takes care about the rest
> and returns #insertedrecord.id# or whatever. Would probably be the
> easiest way to explain to others.
>
> Jochem
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl