agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSQL conversion help
5+ messages / 3 participants
[nested] [flat]
* SQL conversion help
@ 2017-05-20 04:01 =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-05-20 04:07 ` Re: SQL conversion help Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-22 06:30 ` Re: SQL conversion help Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 2 replies; 5+ messages in thread
From: Ertan Küçükoðlu @ 2017-05-20 04:01 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL conversion help
2017-05-20 04:01 SQL conversion help =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
@ 2017-05-20 04:07 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-20 13:24 ` Re: SQL conversion help =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
1 sibling, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2017-05-20 04:07 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL conversion help
2017-05-20 04:01 SQL conversion help =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-05-20 04:07 ` Re: SQL conversion help Adrian Klaver <adrian.klaver@aklaver.com>
@ 2017-05-20 13:24 ` =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-05-20 13:38 ` Re: SQL conversion help Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ertan Küçükoðlu @ 2017-05-20 13:24 UTC (permalink / raw)
To: pgsql-sql
> -----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';
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL conversion help
2017-05-20 04:01 SQL conversion help =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-05-20 04:07 ` Re: SQL conversion help Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-20 13:24 ` Re: SQL conversion help =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
@ 2017-05-20 13:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2017-05-20 13:38 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL conversion help
2017-05-20 04:01 SQL conversion help =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
@ 2017-05-22 06:30 ` Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2017-05-22 06:30 UTC (permalink / raw)
To: pgsql-sql
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;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-05-22 06:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-20 04:01 SQL conversion help =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-05-20 04:07 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-20 13:24 ` =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-05-20 13:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-22 06:30 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox