pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Nocount and scope_identity() 5+ messages / 3 participants [nested] [flat]
* Nocount and scope_identity() @ 2007-02-02 17:49 Demel, Jeff <Jeff.Demel@JavelinDirect.com> 2007-02-02 18:18 ` Re: Nocount and scope_identity() Andrew Sullivan <ajs@crankycanuck.ca> 0 siblings, 1 reply; 5+ messages in thread From: Demel, Jeff @ 2007-02-02 17:49 UTC (permalink / raw) To: pgsql-sql I'm converting a MSSQL query to postgresql. It's something like this: SET NOCOUNT ON; INSERT INTO table_name([list]) VALUES([list]); SELECT SCOPE_IDENTITY() as newId; I get an error on the NOCOUNT statement: "syntax error at or near "on" at character 13" So, I'm wondering if NOCOUNT is supported in Postgres at all. If it's not, what's the alternative? If it is, what is wrong with my syntax? I haven't gotten there yet, but I'm also wondering if the SCOPE_IDENTITY() method is going to work or not. TIA -Jeff This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nocount and scope_identity() 2007-02-02 17:49 Nocount and scope_identity() Demel, Jeff <Jeff.Demel@JavelinDirect.com> @ 2007-02-02 18:18 ` Andrew Sullivan <ajs@crankycanuck.ca> 0 siblings, 0 replies; 5+ messages in thread From: Andrew Sullivan @ 2007-02-02 18:18 UTC (permalink / raw) To: pgsql-sql On Fri, Feb 02, 2007 at 11:49:30AM -0600, Demel, Jeff wrote: > So, I'm wondering if NOCOUNT is supported in Postgres at all. If it's > not, what's the alternative? If it is, what is wrong with my syntax? Not as far as I know. What's it supposed to do? > I haven't gotten there yet, but I'm also wondering if the > SCOPE_IDENTITY() method is going to work or not. I doubt it. What does it do? A -- Andrew Sullivan | ajs@crankycanuck.ca This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nocount and scope_identity() @ 2007-02-02 19:38 Demel, Jeff <Jeff.Demel@JavelinDirect.com> 2007-02-02 19:57 ` Re: Nocount and scope_identity() Andrew Sullivan <ajs@crankycanuck.ca> 0 siblings, 1 reply; 5+ messages in thread From: Demel, Jeff @ 2007-02-02 19:38 UTC (permalink / raw) To: pgsql-sql > So, I'm wondering if NOCOUNT is supported in Postgres at all. If it's > not, what's the alternative? If it is, what is wrong with my syntax? Andrew wrote: Not as far as I know. What's it supposed to do? It suppresses the rowcount returned after the query runs. > I haven't gotten there yet, but I'm also wondering if the > SCOPE_IDENTITY() method is going to work or not. I doubt it. What does it do? It returns the id of the record just inserted. -Jeff This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nocount and scope_identity() 2007-02-02 19:38 Re: Nocount and scope_identity() Demel, Jeff <Jeff.Demel@JavelinDirect.com> @ 2007-02-02 19:57 ` Andrew Sullivan <ajs@crankycanuck.ca> 2007-02-02 20:28 ` Re: Nocount and scope_identity() Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 5+ messages in thread From: Andrew Sullivan @ 2007-02-02 19:57 UTC (permalink / raw) To: pgsql-sql On Fri, Feb 02, 2007 at 01:38:40PM -0600, Demel, Jeff wrote: > Not as far as I know. What's it supposed to do? > > It suppresses the rowcount returned after the query runs. There isn't a way to do that, although there is a way in psql, for example, not to get all that formatting. You want the \pset commands. The count isn't automatically delivered with most APIs (i.e. if you want to know how many rows you touched, you usually have to ask explicitly). > > SCOPE_IDENTITY() method is going to work or not. > I doubt it. What does it do? > > It returns the id of the record just inserted. Ah. Well, there's no in-principle notion if "id" in Postgres (OIDs are optional as of several versions ago). But if you have a primary key that's an integer you populate using a sequence, then you can use the currval() function to get the value of the sequence. There's no race condition there unless you are sharing a pooled connection (and if transactions mean anything to you, you'd better not be doing). A -- Andrew Sullivan | ajs@crankycanuck.ca The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Nocount and scope_identity() 2007-02-02 19:38 Re: Nocount and scope_identity() Demel, Jeff <Jeff.Demel@JavelinDirect.com> 2007-02-02 19:57 ` Re: Nocount and scope_identity() Andrew Sullivan <ajs@crankycanuck.ca> @ 2007-02-02 20:28 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2007-02-02 20:28 UTC (permalink / raw) To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql@postgresql.org, "Demel, Jeff" <Jeff.Demel@JavelinDirect.com> Andrew Sullivan <ajs@crankycanuck.ca> writes: > On Fri, Feb 02, 2007 at 01:38:40PM -0600, Demel, Jeff wrote: >>>> SCOPE_IDENTITY() method is going to work or not. >>> I doubt it. What does it do? >> >> It returns the id of the record just inserted. > Ah. Well, there's no in-principle notion if "id" in Postgres (OIDs > are optional as of several versions ago). But if you have a primary > key that's an integer you populate using a sequence, then you can use > the currval() function to get the value of the sequence. Also, as of 8.2 INSERT RETURNING is a good way to collect the value of an auto-generated key field (or any other auto-generated field...) regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-02-02 20:28 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-02-02 17:49 Nocount and scope_identity() Demel, Jeff <Jeff.Demel@JavelinDirect.com> 2007-02-02 18:18 ` Andrew Sullivan <ajs@crankycanuck.ca> 2007-02-02 19:38 Re: Nocount and scope_identity() Demel, Jeff <Jeff.Demel@JavelinDirect.com> 2007-02-02 19:57 ` Andrew Sullivan <ajs@crankycanuck.ca> 2007-02-02 20:28 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox