PL/PgSQL function returns empty rows instead of rows with ids

Question

I intend to log user ids by policy change in a temporary table. The following should return the logged ids, but it does return empty rows. Somehow the ids are lost :S The row count is okay, the ids are in the table, I don't know what I am doing wrong...

BEGIN;
SELECT * FROM policy_change_update(ARRAY[1,2,3]);
SELECT * FROM user_read_all_by_policy_change();
COMMIT;

The functions:

policy_change_provide_store:

CREATE FUNCTION policy_change_provide_store ()
  RETURNS VOID
AS
  $BODY$
  BEGIN
    CREATE TEMPORARY TABLE policy_change (
      user_id INT4
    );
    EXCEPTION WHEN duplicate_table THEN
    RETURN;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

policy_change_update

CREATE FUNCTION policy_change_update (
  IN user_ids INT4 []
)
  RETURNS VOID
AS
  $BODY$
  BEGIN
    PERFORM policy_change_provide_store();
    INSERT INTO policy_change
    SELECT inserted_user_id FROM unnest(user_ids) AS inserted_user_id
    WHERE NOT EXISTS(SELECT 1 FROM policy_change WHERE user_id = inserted_user_id);
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

user_read_all_by_policy_change

CREATE FUNCTION user_read_all_by_policy_change ()
  RETURNS TABLE (
  user_id INT4
  )
AS
  $BODY$
  BEGIN
    PERFORM policy_change_provide_store();
    RETURN QUERY
    SELECT user_id FROM policy_change ORDER BY user_id DESC;
    IF NOT FOUND THEN
      RAISE SQLSTATE 'UE404' USING MESSAGE = 'not found';
    END IF;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

Any idea?

Same problem as here. Also note the changes in later versions as detailed here. — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 27, 2013 at 0:49

inf3rno · Accepted Answer · 2013-09-27 00:21:07Z

2

The solution:

SELECT policy_change.user_id FROM policy_change ORDER BY policy_change.user_id DESC;

instead of

SELECT user_id FROM policy_change ORDER BY user_id DESC;

answered Sep 27, 2013 at 0:21

inf3rno

26.3k13 gold badges122 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PL/PgSQL function returns empty rows instead of rows with ids

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related