Thread: [SQL] SQL conversion help
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' Thanks & regards, Ertan Küçükoğlu
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 > > Thanks & regards, > Ertan Küçükoğlu > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
> -----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';
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
Ertan Küçükoğlu schrieb am 20.05.2017 um 06:01: > 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' Assuming your primary key column is named ID, you can do it like this: update urtrecetedet u set satirno = t.rn from ( select id, row_number() over (order by id) as rn from urtrecetedet where recetekodu = 'ASD' ) t where t.id = u.id;