Thread: Nocount and scope_identity()
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 thatis privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mailor any attachments by anyone other than the intended recipient, or an employee or agent responsible for deliveringthe message to the intended recipient, is prohibited. If you are not the intended recipient of this message orthe employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replyingto this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of thismessage by unintended recipients is strictly prohibited and may be unlawful.
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
> 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 thatis privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mailor any attachments by anyone other than the intended recipient, or an employee or agent responsible for deliveringthe message to the intended recipient, is prohibited. If you are not the intended recipient of this message orthe employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replyingto this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of thismessage by unintended recipients is strictly prohibited and may be unlawful.
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
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