Subject: Re: SQL conversion help
To: =?UTF-8?B?RXJ0YW4gS8O8w6fDvGtvxJ9sdQ==?= <ertan.kucukoglu@1nar.com.tr>,
 pgsql-sql@postgresql.org
References: <025d01d2d11d$cb2ebba0$618c32e0$@1nar.com.tr>
 <91052907-df30-2c98-a058-3f88dd204059@aklaver.com>
 <000201d2d16c$6e2fb860$4a8f2920$@1nar.com.tr>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <751b5e59-0bac-6bfa-6f93-f82b7db05802@aklaver.com>
Date: Sat, 20 May 2017 06:38:08 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.1.1
MIME-Version: 1.0
In-Reply-To: <000201d2d16c$6e2fb860$4a8f2920$@1nar.com.tr>
Content-Type: text/plain; charset=windows-1254; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 05/20/2017 06:24 AM, Ertan Küçükođlu wrote:
>> -----Original Message-----
>> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
>> Sent: Saturday, May 20, 2017 7:08 AM
>> To: Ertan Küçükođlu <ertan.kucukoglu@1nar.com.tr>;
> pgsql-sql@postgresql.org
>> Subject: Re: [SQL] SQL conversion help
>>
>> On 05/19/2017 09:01 PM, Ertan Küçükođlu wrote:
>>> Hello,
>>>
>>> I have below SQL script used in SQL Server. I would like some help to
>>> convert it into PostgreSQL format, please.
>>>
>>> DECLARE @satirno INT
>>> SET @satirno = 0
>>> UPDATE urtrecetedet
>>> SET @satirno = satirno = @satirno + 1
>>> WHERE recetekodu = 'ASD'
>>
>> I would suggest taking a look at:
>>
>> https://www.postgresql.org/docs/9.6/static/plpgsql.html
>>
>> In particular:
>>
>> https://www.postgresql.org/docs/9.6/static/plpgsql-structure.html
>>
>> https://www.postgresql.org/docs/9.6/static/plpgsql-declarations.html
>>
>> https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html
> 
> Hi Adrian,
> 
> Thanks for documents links. After checking them out. I ended up using
> something like following SQL script instead of a function.
> 
> create sequence if not exists fsatirno;
> alter sequence fsatirno restart;
> update urtrecetedet
>    set satirno = nextval('fsatirno')
>    where recetekodu = 'ASD';

Be aware that a sequence is not guaranteed to provide a gapless sequence 
of numbers:

https://www.postgresql.org/docs/9.6/static/sql-createsequence.html

"
Notes
...

Because nextval and setval calls are never rolled back, sequence objects 
cannot be used if "gapless" assignment of sequence numbers is needed. It 
is possible to build gapless assignment by using exclusive locking of a 
table containing a counter; but this solution is much more expensive 
than sequence objects, especially if many transactions need sequence 
numbers concurrently.

Unexpected results might be obtained if a cache setting greater than one 
is used for a sequence object that will be used concurrently by multiple 
sessions. Each session will allocate and cache successive sequence 
values during one access to the sequence object and increase the 
sequence object's last_value accordingly. Then, the next cache-1 uses of 
nextval within that session simply return the preallocated values 
without touching the sequence object. So, any numbers allocated but not 
used within a session will be lost when that session ends, resulting in 
"holes" in the sequence.

...
"

> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql