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.](http://stackoverflow.com/questions/12166902/postgres-function-null-value-for-row-that-references-new) Also note the [changes in later versions as detailed here.](http://stackoverflow.com/questions/7662731/set-a-default-return-value-for-a-postgres-function/7662787#7662787) — Erwin Brandstetter, Sep 27 '13 at 00:49

score 2 · Accepted Answer · answered Sep 27 '13 at 00:21

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 '13 at 00:21

inf3rno

24,976
11
115
197

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

1 Answers1