As far as I can tell, my function properly resembles the samples I've seen. Can someone clue me in as to how I get this to work?
create or replace function get_user_by_username(
username varchar(250),
online boolean
) returns setof record as $$
declare result record;
begin
if online then
update users
set last_activity = current_timestamp
where user_name = username;
end if;
return query
select
user_id,
user_name,
last_activity,
created,
email,
approved,
last_lockout,
last_login,
last_password_changed,
password_question,
comment
from
users
where
user_name = username
limit 1;
return;
end;
$$ language plpgsql;
if you would like to create function returning setof record, you'll need to define column types in your select statement
Your query should look something like this:
select * from get_user_by_username('Username', True) as
f(user_id integer, user_name varchar, last_activity, varchar, created date, email archar, approved boolean, last_lockout timestamp, last_login timestamp,
last_password_changed timestamp, password_question varchar, comment varchar)
(you will probably need to change the data types)
I personaly prefer the types approach. it assures that if the function is edited, all the queries will return correct results. It might be a pain because every time you modify function's arguments you'll need to recreate/drop types aswell tho.
Eg:
CREATE TYPE return_type as
(user_id integer,
user_name varchar,
last_activity varchar,
created timestamp,
email varchar,
approved boolean,
last_lockout timestamp ,
last_login timestamp,
last_password_changed timestamp,
password_question varchar,
comment varchar);
create or replace function get_user_by_username( username varchar(250), online
boolean) returns setof return_type as $$
declare _rec return_type;
begin
if online then
update users
set last_activity = current_timestamp
where user_name = username;
end if;
for _rec in select
user_id,
user_name,
last_activity,
created,
email,
approved,
last_lockout,
last_login,
last_password_changed,
password_question,
comment
from
users
where
user_name = username
limit 1
loop
return next _rec;
end loop
end;
$$ language plpgsql;