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
