PostgresQL equivalent of NOCOUNT

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/

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

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

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

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:

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

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.