Обсуждение: increment counter in VIEW
Dear All, Does anybody know how to add increment counter column to VIEW? To get something like this cntrColumn | anotherCol ------------------------ 1 | val1 2 | val2 3 | .. 4 | The best if this counter will be executed together with sort to ensure that sertain record gets always the same Counter value. Thanks a lot in advance Oleg
> Dear All, > Does anybody know how to add increment counter column to VIEW? To get > something like this > > cntrColumn | anotherCol > ------------------------ > 1 | val1 > 2 | val2 > 3 | .. > 4 | > > The best if this counter will be executed together with sort to ensure > that sertain record gets always the same Counter value. I am not sure if generate_series is what you want. http://www.postgresql.org/docs/8.1/interactive/functions-srf.html Regards, Richard Broersma Jr.
Dear Richard,
Thank you very much for your link. It looks like it could do the job I want.
Unfortunately I have problem with inserting this functionality into my VIEW
I tried:
CREATE OR REPLACE VIEW my_view AS SELECT
S.a AS test,
knoten."GEOMETRY",
knoten.knoten,
FROM
generate_series(1,300) AS S(a),
knoten knoten
WHERE
knoten.knoten::text = knoten_flaeche.knoten::text;
Without generate_series my View produces 300 records. if I insert
generate_series then I get 90000 because it tries all possible
combinations with S.a. I do not know how can I limit it with WHERE.
I also tried to work with SEQUENCES but each time we update sequence it
continue to count from the last number. For example from 1-300 then from
300-600, etc.
We would be very grateful if you or somebody on this list could help us
further.
Thanks a lot in advance,
Oleg
Richard Broersma Jr schrieb:
>> Dear All,
>> Does anybody know how to add increment counter column to VIEW? To get
>> something like this
>>
>> cntrColumn | anotherCol
>> ------------------------
>> 1 | val1
>> 2 | val2
>> 3 | ..
>> 4 |
>>
>> The best if this counter will be executed together with sort to ensure
>> that sertain record gets always the same Counter value.
>>
>
> I am not sure if generate_series is what you want.
> http://www.postgresql.org/docs/8.1/interactive/functions-srf.html
>
> Regards,
> Richard Broersma Jr.
>
>
> Thank you very much for your link. It looks like it could do the job I want. > Unfortunately I have problem with inserting this functionality into my VIEW > I tried: > CREATE OR REPLACE VIEW my_view AS SELECT > S.a AS test, > knoten."GEOMETRY", > knoten.knoten, > FROM > generate_series(1,300) AS S(a), > knoten knoten > WHERE > knoten.knoten::text = knoten_flaeche.knoten::text; > > Without generate_series my View produces 300 records. if I insert > generate_series then I get 90000 because it tries all possible > combinations with S.a. I do not know how can I limit it with WHERE. maybe this link might help. http://archives.postgresql.org/pgsql-sql/2006-03/msg00143.php Regards, Richard Broersma Jr.
On Wed, Jul 12, 2006 at 17:03:01 +0200, Oleg <evdakov@iwg.uka.de> wrote: > > Without generate_series my View produces 300 records. if I insert > generate_series then I get 90000 because it tries all possible > combinations with S.a. I do not know how can I limit it with WHERE. I don't think generate_series will work well in this case. If you can do the numbering in the application, that will probably be the easiest thing to do. Otherwise you are going to need to get the numbers into the underlying table or another table that can be joined to the underlying table on a key. And these numbers will need to be kept up to date. (And deletions will be, expensive since that will require renumbering rows with higher numbers.) This also will cause problems for concurrent updates.
Dear All,
Thank you very much for your answers. I just want to summarize:
1. create sequence:
CREATE SEQUENCE myseq;
2. insert sequence function calls into VIEW:
CREATE OR REPLACE VIEW my_view AS SELECT
nextval('myseq')::text AS test,
knoten.knoten,
FROM
setval('myseq',1),
knoten knoten
WHERE
knoten.knoten::text = knoten_flaeche.knoten::text;
In Postgres it seems to work fine, but not yet in my application.
My doubt is about setval('myseq',1). I am not sure that it will always
works fine when I access view from my application. For example when I
call on_update rule.
Oleg
Oleg schrieb:
> Dear Richard,
> Thank you very much for your link. It looks like it could do the job I
> want.
> Unfortunately I have problem with inserting this functionality into my
> VIEW
> I tried:
> CREATE OR REPLACE VIEW my_view AS SELECT
> S.a AS test,
> knoten."GEOMETRY",
> knoten.knoten,
> FROM
> generate_series(1,300) AS S(a),
> knoten knoten
> WHERE
> knoten.knoten::text = knoten_flaeche.knoten::text;
>
> Without generate_series my View produces 300 records. if I insert
> generate_series then I get 90000 because it tries all possible
> combinations with S.a. I do not know how can I limit it with WHERE.
>
> I also tried to work with SEQUENCES but each time we update sequence
> it continue to count from the last number. For example from 1-300 then
> from 300-600, etc.
>
> We would be very grateful if you or somebody on this list could help
> us further.
>
> Thanks a lot in advance,
> Oleg
>
>
> Richard Broersma Jr schrieb:
>>> Dear All,
>>> Does anybody know how to add increment counter column to VIEW? To
>>> get something like this
>>>
>>> cntrColumn | anotherCol
>>> ------------------------
>>> 1 | val1
>>> 2 | val2
>>> 3 | ..
>>> 4 |
>>>
>>> The best if this counter will be executed together with sort to
>>> ensure that sertain record gets always the same Counter value.
>>>
>>
>> I am not sure if generate_series is what you want.
>> http://www.postgresql.org/docs/8.1/interactive/functions-srf.html
>>
>> Regards,
>> Richard Broersma Jr.
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
> Dear Richard,
>Thanks a lot for your link. I am trying it right now.
>Back to the other method. My collegue has tried a bit different sintax:
>
>CREATE OR REPLACE VIEW my_view AS SELECT
> (select setval('test_counter',1)) AS "i",
> nextval('test_counter') AS "FID",
> knoten.knoten
>FROM
> knoten knoten
>WHERE
> knoten.knoten::text = knoten_flaeche.knoten::text;
>
>This produeces same result as previous one.
>As we figured out, problem with calling it from application isdifferent. So we probably will have
>to solve it from application (java)side.
>We have 5 columns in the view that are primary key. Application howevermust/can accept only one
>column as primary key to be used in WHERE toperform UPDATE. So we will probubly change
>application to make WHEREwork with many columns.
>
The result you are getting are expected since you haven't specified a join.
If you are are still getting a "cross join" between your sequence and your data, there is one way
that will get you what you want.
http://www.postgresql.org/docs/8.1/interactive/plpgsql.html
You can use the plpgsql procedural functions. By using a cursor, you can iterate through the
select query and append the sequencial number to the result set before you return the set to the
client.
It is more work but it will get you what you want. Still, it is too bad that you can't find a way
to join the sequence directly to your query.
May you could give the sql function one more try.
CREATE FUNCTION my_funct () RETURNS SETOF knoten AS $$
SELECT setval('test_counter',1));
SELECT
( SELECT nextval('test_counter')
where
) as "FID",
knoten.knoten
FROM
knoten knoten
WHERE
knoten.knoten::text = knoten_flaeche.knoten::text;
$$ LANGUAGE SQL;
I am not sure if this will work because I am unable to test it at the moment.
Regards,
Richard Broersma jr.