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:
- users
- id – INTEGER
- username – VARCHAR
- first_name – VARCHAR
- last_name – VARCHAR
- email_address – VARCHAR
- is_deleted – BOOLEAN
- credentials
- id – INTEGER
- name (something like admin, backend, frontend, etc.) – VARCHAR
- 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]);
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