plpgsql: some 'DELETE' queries are returning ROWCOUNT = 0

[Dragos Valentin Moinescu <dragos.moinescu@xxxxxxxxx>]

Hello,

I try to create some functions that will execute some queries stored
inside tables.
These 2 functions are:

[code]

CREATE FUNCTION __syncpg_execute1(sql text) RETURNS text AS $$
DECLARE
        nraffected      integer;
        retcode         text;
BEGIN
        retcode := '';
        EXECUTE sql;
        GET DIAGNOSTICS nraffected = ROW_COUNT;
        IF nraffected = 0 THEN
                retcode = 'rows_affected = 0';
        END IF;

        RETURN retcode;
EXCEPTION
        WHEN OTHERS THEN
                RETURN SQLSTATE;
END;
$$ LANGUAGE plpgsql;

[/code]

and

[code]

CREATE FUNCTION __syncpg_execute() RETURNS integer AS $$
DECLARE
        sqlrec text;
        nrrows integer;
        sqlins text;
        errstr text;
BEGIN
        nrrows := 0;

        FOR sqlrec IN SELECT sql FROM __syncpg_toexecute LOOP
                SELECT __syncpg_execute1(sqlrec) INTO errstr;
                nrrows := nrrows + 1;
                IF errstr <> '' THEN
                        sqlins = 'INSERT INTO __syncpg_toexecuteres VALUES (' ||
                                quote_literal(sqlrec) || ', ' ||
                                quote_literal(errstr) || ');';
                        EXECUTE sqlins;
                END IF;
        END LOOP;

        TRUNCATE __syncpg_toexecute;

        RETURN nrrows;
END;
$$ LANGUAGE plpgsql;

[/code]

The problem I have is that some DELETE FROM ... does not affect rows
even if those rows are there. I get the same issue with UPDATE ....

I haven't found issues with INSERT (there are lots of errors=23505 -
which is unique violation)

If I run that SQL by hand it runs successfully deleting/updating the
necessary rows.

Am I missing something?

Thank you


-- 
Best regards,
Dragos Moinescu

-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin