hamann.w@xxxxxxxxxxx wrote:
> a) I am running some select query
> select ... order by ....
> Now, I would like to preserver the ordering through further processing by adding a sequence number
> Of course I can do:
> create temp sequence mseq;
> select xx.*, nextval('mseq') as ord from (select ... order by ....) xx;
> drop sequence mseq;
> Is there a simpler way (avoiding the create/drop parts)
A window function would be the best thing:
SELECT ...,
row_number() OVER (ORDER BY ...)
FROM ...
> b) can a sql function return the count of affected rows of some query?
> create function merge_names(int, int) returns void as
> $_$
> update namelinks set nid = $2 where nid = $1;
> -- want the affected rows of the above query
> delete from names where nid = $1
> -- return result here
> $_$
> language sql;
You cannot do it in an SQL function.
In PL/pgSQL you can use
GET DIAGNOSTICS avariable = ROW_COUNT;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
