Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Has the player been banned?
- select true from pref_ban where id=?
- -- What is the reputation of this player?
- select
- count(nullif(nice, false)) -
- count(nullif(nice, true)) as rep
- from pref_rep where id=?
- -- Is he or she a special VIP player?
- select vip > now() as vip from pref_users where id=?
- -- How many games has the player played to the end?
- select completed from pref_match where id=?
- create or replace function get_user_info(_id varchar) returns XXX as $BODY$
- declare
- is_banned boolean;
- reputation integer;
- is_vip boolean;
- completed_games integer;
- begin
- select 1 into is_banned from pref_ban where id=_id;
- select
- count(nullif(nice, false)) -
- count(nullif(nice, true))
- into reputation
- from pref_rep where id=_id;
- select vip > now() into is_vip from pref_users where id=_id;
- select completed into completed_games from pref_match where id=_id;
- return XXX; /* How to return 4 values here? */
- end;
- $BODY$ language plpgsql;
- create or replace function get_user_info(
- IN _id varchar,
- OUT is_banned boolean,
- OUT reputation integer,
- OUT is_vip boolean,
- OUT completed_games integer
- )
- -- no returns clause necessary, output structure controlled by OUT parameters
- -- returns XXX
- as $BODY$
- begin
- select true into is_banned from pref_ban where id=_id;
- select
- count(nullif(nice, false)) -
- count(nullif(nice, true))
- into reputation
- from pref_rep where id=_id;
- select vip > now() into is_vip from pref_users where id=_id;
- select completed into completed_games from pref_match where id=_id;
- -- no return statement necessary, output values already stored in OUT parameters
- -- return XXX;
- end
- $BODY$ language plpgsql;
- -- this will return all properties (columns) from your function:
- select * from get_user_info();
- -- these will return one property (column) from your function:
- select is_banned from get_user_info();
- select (get_user_info()).is_banned;
- create type user_type as (
- is_banned boolean,
- reputation integer,
- is_vip boolean,
- completed_games integer);
- create or replace function check_user_type ()
- returns user_type language plpgsql as $$
- declare
- rec user_type;
- begin
- select true into rec.is_banned;
- select 100 into rec.reputation;
- select false into rec.is_vip;
- select 22 into rec.completed_games;
- -- you can do the same in a little bit nicer way:
- -- select true, 100, false, 22 into rec
- return rec;
- end $$;
- select * from check_user_type();
- create or replace function check_set_of_user_type ()
- returns setof user_type language plpgsql as $$
- declare
- rec user_type;
- begin
- for rec in
- select i/2*2 = i, i, i < 3, i+ 20
- from generate_series(1, 4) i
- loop
- return next rec;
- end loop;
- return query
- select true, 100+ i, true, 100+ i
- from generate_series(1, 2) i;
- end $$;
- select * from check_set_of_user_type();
- is_banned | reputation | is_vip | completed_games
- -----------+------------+--------+-----------------
- f | 1 | t | 21
- t | 2 | t | 22
- f | 3 | f | 23
- t | 4 | f | 24
- t | 101 | t | 101
- t | 102 | t | 102
Add Comment
Please, Sign In to add comment