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
