Skip site navigation (1) Skip section navigation (2)

Re: increment counter in VIEW

From: Oleg <evdakov(at)iwg(dot)uka(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: increment counter in VIEW
Date: 2006-07-13 09:55:52
Message-ID: 44B618A8.4090801@iwg.uka.de (view raw or flat)
Thread:
Lists: pgsql-novice
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
>


In response to

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-07-13 13:55:13
Subject: Fwd: Re: increment counter in VIEW
Previous:From: Andrej Ricnik-BayDate: 2006-07-13 08:26:42
Subject: Re: The name of the game (was Re: postgre linkage with non-postgre db)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group