Postgres: how to tell if insert took place I have a (working) stored procedure that incorporates logic disallowing dups. However, I cant determine a way to have the stored procedure tell me if a row was inserted or not. I can think of a terrible hack - the table has a sequence, and compare the sequence current value before and after the insert.  At least I think its a hideous hack...

CREATE OR REPLACE FUNCTION mysp() RETURNS integer AS
$BODY$
DECLARE
BEGIN
    -- only 1 row gets in
    INSERT INTO mytable (locationname) VALUES ('some text')
    WHERE NOT EXISTS (SELECT 1 FROM mytable
    WHERE locationname='some text');
    -- cant figure out how to do something like RETURN ROWSINSERTED();
END
$BODY$
LANGUAGE 'plpgsql' ;
Try this

 CREATE OR REPLACE FUNCTION mysp() RETURNS integer AS
 $BODY$
 DECLARE
 aff_rows numeric;
 BEGIN
     execute 'INSERT INTO mytable (locationname) VALUES (''some text'') WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE locationname=''some text'')';
     GET DIAGNOSTICS aff_rows = ROW_COUNT;
     raise info 'affected rows :: %', aff_rows;
     return aff_rows;
 END
 $BODY$
 LANGUAGE 'plpgsql' ;


Source: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

(refer section: 39.5.5. Obtaining the Result Status)