PostgreSQL – How to Select Number of Affected Rows to Variable in Function

postgresql

create or replace function test()
returns void as $$
begin
  update tbl set col1 = true where col2 = false;
  -- now I want to raise exception if update query affected more than 2 rows to rollback the update
end;
$$ language plpgsql;

How I can select number of affected rows to a variable in function?

Best Answer

create or replace function test()
returns void as $$
declare
    v_cnt numeric;
begin
    v_cnt := 0;

        update tbl set col1 = true where col2 = false;
        GET DIAGNOSTICS v_cnt = ROW_COUNT;

        if v_cnt > 1 then
           RAISE EXCEPTION 'more than one row affected --> %', v_cnt; 
        end if;
end;
$$ language plpgsql;

Related Solutions

Postgresql – Select consecutive number of rows starting from some number

You can try something like

SELECT field1, field2 
FROM table1 
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.

Postgresql – GROUP BY and ORDER BY problem

First off, your PRIMARY KEY spanning two varchar(2000) columns seems extremely expensive. If you use your PK for anything else I suggest a surrogate PK (use a serial column) and add a UNIQUE constraint to enforce uniqueness on (cid, name, synonym).

If one of your varchar columns actually uses the maximum length you would exceed the maximum size for an index entry. See:

Character varying index overhead & length limit

I guess what you want is this, because it would make sense:

SELECT DISTINCT ON (c.name)
       c.name, min(s.synonym) AS min_synonym, s.cid
     , ts_rank(s.tsv_syns, c.lexemes, 16) AS rnk
     , count(*) AS ct
FROM   synonyms_all_gin_tsvcolumn s
JOIN   cmap5                      c ON c.lexemes @@ s.tsv_syns
GROUP  BY c.name, rnk, s.cid
ORDER  BY c.name, rnk DESC, ct DESC;

I use explicit [INNER] JOIN with attached join condition replacing your CROSS JOIN plus WHERE clause. It's generally considered superior (easier to read and debug). I also use rnk as column name to avoid the basic function name rank as identifier.
Group the results per c.name that have the same rnk and s.cid, take min(s.synonym) (for lack of definition in the question), and count(*) the peers per group.
Reduce to one row per c.name with DISTINCT ON (Postgres specific extension of SQL standard DISTINCT), taking the highest rank first and, within same rank, the highest peer count. See:
Select first row in each GROUP BY group?

Combining GROUP BY and DISTINCT ON this way in one query level is possible since DISTINCT or DISTINCT ON are applied after GROUP BY.

Best Answer

Related Solutions

Postgresql – Select consecutive number of rows starting from some number

Postgresql – GROUP BY and ORDER BY problem

Related Question