Re: sql questions

On Fri, Jul 20, 2018 at 4:27 AM <hamann(dot)w(at)t-online(dot)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)