Re: increment counter in VIEW

> 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.