PostgreSQL – Example Query As A Function

One of the things that I am not all that thrilled about is the fact that at times it is hard to find examples of PostgreSQL queries or plpgsql functions. For this reason, I want to help out anyone who would like to see an example of converting a query into a function:

CREATE OR REPLACE FUNCTION get_user_credentials(
  IN i_user_ids INTEGER[],
  OUT o_id users.id%TYPE,
  OUT o_username users.username%TYPE,
  OUT o_first_name users.first_name%TYPE,
  OUT o_last_name users.last_name%TYPE,
  OUT o_email_address users.email_address%TYPE,
  OUT o_credentials TEXT
) RETURNS SETOF RECORD
  VOLATILE
AS $$
BEGIN
  RETURN QUERY
    SELECT id,
      username,
      first_name,
      last_name,
      email_address,
      ARRAY_TO_STRING(ARRAY(
        SELECT c.name
        FROM credentials AS c
        JOIN user_credentials AS uc
        ON c.id = uc.credential_id
        AND NOT uc.is_deleted
        AND uc.user_id = users.id
      ), ', ')
    FROM users
    WHERE id = ANY(i_user_ids)
    AND NOT is_deleted;
END;
$$ LANGUAGE plpgsql;

This function would work assuming that you have the following tables defined:

1. users
• id – INTEGER
• username – VARCHAR
• first_name – VARCHAR
• last_name – VARCHAR
• email_address – VARCHAR
• is_deleted – BOOLEAN
2. credentials
• id – INTEGER
• name (something like admin, backend, frontend, etc.) – VARCHAR
3. user_credentials
• user_id – INTEGER
• credential_id – INTEGER
• is_deleted – BOOLEAN

The following would be an example call to this function that can return two lines if there are users with the ID of 1 and 5:

SELECT *
FROM get_user_credentials(ARRAY[1,5]);