Thread: Problems with GET DIAGNOSTICS SELECT PROCESSED INTO result;

It doesn't seem to work as advertised:

DROP TABLE junk;
CREATE TABLE junk (something INT4);

DROP FUNCTION testfunction(INT4);
CREATE FUNCTION testfunction(INT4) RETURNS INT4 AS '
DECLARE
    result INT4;
BEGIN
    INSERT INTO junk (something) VALUES ($1);
    GET DIAGNOSTICS SELECT PROCESSED INTO result;
    RETURN result;
END;
' LANGUAGE 'plpgsql';

When I then run it I get:

=> select testfunction(10);
NOTICE:  plpgsql: ERROR during compile of testfunction near line 5
ERROR:  parse error at or near "SELECT"

Any idea how to get this to work?  This is with PG7.1.2.

Thanks

I got the syntax for that by searching around on mailing list
archives.  I should have searched the PG documentation, because this
definitely works:

DROP TABLE junk;
CREATE TABLE junk (something INT4);

DROP FUNCTION testfunction(INT4);
CREATE FUNCTION testfunction(INT4) RETURNS INT4 AS '
DECLARE
    result INT4;
BEGIN
    INSERT INTO junk (something) VALUES ($1);
    GET DIAGNOSTICS result = ROW_COUNT;
    RETURN result;
END;
' LANGUAGE 'plpgsql';

So I guess the syntax has changed a little bit between beta and
released versions.

This is a very useful (essential) feature, because let's say that you
have a table with some constraints or integrity checks, and you try to
insert something which for whatever reason may violate those
constraints.  The script needs to have some way of knowing that the
insert failed.  So this is a great feature to have.

Thanks Postgres!