1

I created a PL/pgSQL function in PostgreSQL 10.4 using pgAdmin. It returns a query and updates entries in the same table. Called in pgAdmin, it runs as expected. When run from external code, the table is returned, but the updates never run. Wondering if this has to do with how I wrote the function?

CREATE OR REPLACE FUNCTION report()
RETURNS TABLE(id text, t1 text, t2 text)
LANGUAGE 'plpsql'

AS $BODY$
DECLARE
rec RECORD;

BEGIN
RETURN QUERY
SELECT id,
name AS t1,
data AS t2
FROM table1
WHERE status IS NULL;

IF FOUND THEN
FOR rec IN SELECT id
FROM table1
WHERE status IS NULL
LOOP
UPDATE table1 SET val=val+1
WHERE id=rec.id;
END IF;

RETURN;
END
$BODY$;

EDIT:

Thanks for the replies. Don't have anyone else to help take a look, so this was very helpful with troubleshooting what was going on.

Was so fixated on my PL/pgSQL function as having the issue, I overlooked my external program running the function. User error on my part. After moving my UPDATE above my SELECT, I noticed my program was seeing the UPDATE. I was forgetting to COMMIT the updates back to the database in my external program, so the table was never updating. Added a commit and good to.

Tony
  • 13
  • 3
  • There is no "pgSQL". Do you mean plpgsql? Please (*always!*) provide your version of Postgres. `SELECT version();` helps. And a *complete* function definition, even if it's not working. – Erwin Brandstetter Jan 04 '19 at 04:11
  • 1
    Is this your actual function now? `status=NULL` is always NULL and will never return any rows. You want `status IS NULL` instead. Do you have concurrent write access? – Erwin Brandstetter Jan 04 '19 at 06:00
  • You are correct. I wasn't able to directly copy/paste my code, so I was having to re-type it out and made that typo. I'm not 100% about the concurrent write access, but I believe so. Will have to double check. – Tony Jan 04 '19 at 14:54

1 Answers1

0

It seems like you can replace the whole function with this plain, much cheaper UPDATE query and a RETURNING clause:

UPDATE table1
SET    val = val + 1
WHERE  status IS NULL
RETURNING id, name AS t1, data AS t2;

If there can be race conditions, consider:

You can run this query as is, without function wrapper. If you want a function wrapper, prepend RETURN QUERY to return results directly. Code example:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • My apologize, should have thought this through more. At this point, I'm basically playing with this function. The goal is to run the function from external code to process the data returned from the select, but then update each entry to mark it was touched essentially. Added the full create and PostgreSQL version the database is running. I'll also give what you posted a shot. – Tony Jan 04 '19 at 04:36
  • More complete thought on the intended goal. External code will call that function to find any records with a status attribute of NULL that it will then process. I then want the function to update a counter attribute for each record to indicate how many times they have been processed by the external code. – Tony Jan 04 '19 at 05:57
  • Gave that a go, but running into an error stating no destination for result data. However, I think I figured out what is happening. Running the function from my external code is running everything correctly; but it isn't committing the updates to the table. I switched the logic to process the UPDATES first then RETURN QUERY. My code saw the updated values, but the table itself didn't update. – Tony Jan 04 '19 at 19:27
  • @Tony: I added more above to address the error you saw. – Erwin Brandstetter Jan 04 '19 at 20:19
  • Much appreciated. Likely no surprise, PL/pgSQL is very new to me. Took a look at that new post and that make sense, and looks nicer then what I currently have. – Tony Jan 06 '19 at 04:48