Thread: sql questions
Hi,
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)
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;
Best regards
Wolfgang Hamann
hamann.w@t-online.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
Zitat von hamann.w@t-online.de:
> 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)
Can't you just do the ordering at the end of the processing? Maybe you
need to drag along the order by columns and just dump them at the very
end if applicable.
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
Attachment
On Fri, Jul 20, 2018 at 4:27 AM <hamann.w@t-online.de> wrote:
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;
Yes. You can do this in pure SQL by using CTEs like the following example.
with myupdate as (
update test set a = 4 where a = 1
returning a
),
mydelete as (
delete from testnames where nid = 1
)
select count(1) from myupdate;
You can then just wrap a function around this. Full test case below.
-- Create test tables
create table test ( a integer );
insert into test values (1),(1),(3);
create table testnames ( nid integer );
insert into testnames values (1);
-- Update, delete, and return the number of updates in a single statement
create function test_names(integer, integer) returns bigint as
$_$
with myupdate as (
update test set a = $2 where a = $1
returning a
),
mydelete as (
delete from testnames where nid = $1
)
select count(1) from myupdate
$_$
language sql;
-- Run it
# select test_names(1,4);
test_names
------------
2
(1 row)
-- Verify results
=# select * from test;
a
---
3
4
4
(3 rows)
=# select * from testnames;
nid
-----
(0 rows)
>> Zitat von hamann.w@t-online.de:
>>
>> > 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)
>>
>> Can't you just do the ordering at the end of the processing? Maybe you
>> need to drag along the order by columns and just dump them at the very
>> end if applicable.
>>
Hi,
in this specific case every search result consists of a pair of related entries that are not close to each other in
ordering. So I order by first entry and use the row number to keep the second entry next to the first one,
BTW: the use case is scanning a database of people for duplicates. Whenever there are 3 or more
components in a name, the split betwwen first and last name can be ambiguous, and so its is common to find
both "Ludwig" "van Beethoven" and "Ludwig van" "Beethoven"
Best regards
WOlfgang