Re: sql questions

hamann(dot)w(at)t-online(dot)de 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