Postgres Pro
Downloads
Home   >   mailing lists

Thread: Onfly, function generated ID for Select Query

Onfly, function generated ID for Select Query

From
Durumdara
Date:
Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where ....

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd

SV: Onfly, function generated ID for Select Query

From
Gustavsson Mikael
Date:

Hi,


Is it something like row_number() you want?https://www.postgresql.org/docs/current/functions-window.html

KR

Mikael
Från: Durumdara <durumdara@gmail.com>
Skickat: den 10 mars 2023 8:12
Till: Postgres General
Ämne: Onfly, function generated ID for Select Query
 
Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where ....

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd

Re: Onfly, function generated ID for Select Query

From
Durumdara
Date:
 Dear Mikael!

Wooooow... that is it!
Thank you!

SELECT product_id, product_name, group_id, ROW_NUMBER () OVER (ORDER BY product_id) FROM products;

Does this mean that I have to duplicate the order by clause?

SELECT product_id, product_name, group_id, ROW_NUMBER () 
    OVER (ORDER BY product_id, product_name, group_id) 
FROM products 
ORDER BY product_id, product_name, group_id

To get the same sequence in the ROW_ID-s, and the Query rows?

BR
dd

Gustavsson Mikael <mikael.gustavsson@smhi.se> ezt írta (időpont: 2023. márc. 10., P, 8:33):

Hi,


Is it something like row_number() you want?https://www.postgresql.org/docs/current/functions-window.html

KR

Mikael
Från: Durumdara <durumdara@gmail.com>
Skickat: den 10 mars 2023 8:12
Till: Postgres General
Ämne: Onfly, function generated ID for Select Query
 
Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where ....

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd

SV: Onfly, function generated ID for Select Query

From
Gustavsson Mikael
Date:

No, the result will be ordered by the window functions order clause so no additional ordering is nessesary.
You can try this by changing it to OVER(ORDER BY product_name)


KR

Mikael

Från: Durumdara <durumdara@gmail.com>
Skickat: den 10 mars 2023 08:41:06
Till: Gustavsson Mikael
Kopia: Postgres General
Ämne: Re: Onfly, function generated ID for Select Query
 
 Dear Mikael!

Wooooow... that is it!
Thank you!

SELECT product_id, product_name, group_id, ROW_NUMBER () OVER (ORDER BY product_id) FROM products;

Does this mean that I have to duplicate the order by clause?

SELECT product_id, product_name, group_id, ROW_NUMBER () 
    OVER (ORDER BY product_id, product_name, group_id) 
FROM products 
ORDER BY product_id, product_name, group_id

To get the same sequence in the ROW_ID-s, and the Query rows?

BR
dd

Gustavsson Mikael <mikael.gustavsson@smhi.se> ezt írta (időpont: 2023. márc. 10., P, 8:33):

Hi,


Is it something like row_number() you want?https://www.postgresql.org/docs/current/functions-window.html

KR

Mikael
Från: Durumdara <durumdara@gmail.com>
Skickat: den 10 mars 2023 8:12
Till: Postgres General
Ämne: Onfly, function generated ID for Select Query
 
Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where ....

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd