pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedworkaround for missing ROWNUM feature with the help of GUC variables
3+ messages / 2 participants
[nested] [flat]
* workaround for missing ROWNUM feature with the help of GUC variables
@ 2010-08-04 10:35 Marc Mamin <M.Mamin@intershop.de>
2010-08-04 20:07 ` Re: workaround for missing ROWNUM feature with the help of GUC variables Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 3+ messages in thread
From: Marc Mamin @ 2010-08-04 10:35 UTC (permalink / raw)
To: pgsql-sql
Hello,
here my two pence on this recurring thema.
(just a workaround)
regards,
Marc Mamin
The PG parameter must be set to allow defining own configuration
variables:
#-----------------------------------------------------------------------
---
# CUSTOMIZED OPTIONS
#-----------------------------------------------------------------------
---
custom_variable_classes = 'public' # list of custom
variable class names
usage example:
--------------------
select my_rownum(),* from generate_series (10,15);
wrong usage:
--------------------
select my_rownum() as n1,
my_rownum() as n2,
*
from generate_series (10,15);
solution:
--------------------
select my_rownum('1') as n1,
my_rownum('2') as n2,
*
from generate_series (10,15);
Code:
=====
CREATE OR REPLACE FUNCTION public.my_rownum ()
returns int AS
$BODY$
/*
equivalent to oracle rownum
(The previous row value is attached to a GUC Variable valid in the
current transaction only)
quite slow :-(
*/
DECLARE
current_rownum int;
config_id varchar = 'public.my_rownum';
BEGIN
BEGIN
current_rownum := cast (current_setting (config_id) as int);
EXCEPTION when others then
return cast( set_config(config_id, cast(1 as text), true) as int);
END;
RETURN cast( set_config(config_id, cast(current_rownum + 1 as text),
true) as int);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
/*
------------------------------------------------------------------------
------------------
For multiple usage:
------------------------------------------------------------------------
------------------ */
CREATE OR REPLACE FUNCTION public.my_rownum ( id varchar )
returns int AS
$BODY$
/*
equivalent to oracle rownum
quite slow :-(
(The previous row value is attached to a GUC Variable valid in the
current transaction only)
$1: when more than one my_rownum is used within a query, each call
must have its own ID in order to get different GUC variable).
*/
DECLARE
current_rownum int;
config_id varchar = 'public.my_rownum'||id;
BEGIN
BEGIN
current_rownum := cast (current_setting (config_id) as int);
EXCEPTION when others then
return cast( set_config(config_id, cast(1 as text), true) as int);
END;
RETURN cast( set_config(config_id, cast(current_rownum + 1 as text),
true) as int);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: workaround for missing ROWNUM feature with the help of GUC variables
2010-08-04 10:35 workaround for missing ROWNUM feature with the help of GUC variables Marc Mamin <M.Mamin@intershop.de>
@ 2010-08-04 20:07 ` Andreas Joseph Krogh <andreak@officenet.no>
2010-08-06 12:19 ` Re: workaround for missing ROWNUM feature with the help of GUC variables Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas Joseph Krogh @ 2010-08-04 20:07 UTC (permalink / raw)
To: pgsql-sql
On 08/04/2010 12:35 PM, Marc Mamin wrote:
>
> Hello,
>
> here my two pence on this recurring thema.
>
> (just a workaround)
>
>
I don't understand what you mean by missing ROWNUM feature, PG got this
with windows-functions in 8.4:
http://www.postgresql.org/docs/8.4/interactive/functions-window.html
Example:
select username, row_number() over() from my_user_table;
username | row_number
-------------+------------
admin | 1
everyone | 2
--
Andreas Joseph Krogh<andreak@officenet.no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: workaround for missing ROWNUM feature with the help of GUC variables
2010-08-04 10:35 workaround for missing ROWNUM feature with the help of GUC variables Marc Mamin <M.Mamin@intershop.de>
2010-08-04 20:07 ` Re: workaround for missing ROWNUM feature with the help of GUC variables Andreas Joseph Krogh <andreak@officenet.no>
@ 2010-08-06 12:19 ` Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 3+ messages in thread
From: Marc Mamin @ 2010-08-06 12:19 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; pgsql-sql
> I don't understand what you mean by missing ROWNUM feature, PG got
this with windows-functions in 8.4:
> http://www.postgresql.org/docs/8.4/interactive/functions-window.html
<http://www.postgresql.org/docs/8.4/interactive/functions-window.html;
I'm unfortunately still using 8.3.
sorry not to have mentioned that.
Marc Mamin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-08-06 12:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-04 10:35 workaround for missing ROWNUM feature with the help of GUC variables Marc Mamin <M.Mamin@intershop.de>
2010-08-04 20:07 ` Andreas Joseph Krogh <andreak@officenet.no>
2010-08-06 12:19 ` Marc Mamin <M.Mamin@intershop.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox