On Fri, Jul 20, 2018 at 4:27 AM <hamann.w@xxxxxxxxxxx> 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)
