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]);
Categories: BlogSQL

1 Comment

Mike Marshall · January 11, 2014 at 9:59 AM

+1
Good to see Postgres is still in your life! : )

Check out this repo, lot’s of great examples for future reference.

https://github.com/WojciechMula/wikibooks-plpgsql/tree/master/sql


Mike
Postgres / Mongodb Dba

Leave a Reply to Mike Marshall Cancel reply

Your email address will not be published. Required fields are marked *