At 12:20 PM 25/07/2005 +0000, you wrote:

>Hello!
>I created stored procedure that returns 0/1 depending if a user has a
>role or not. HasRole(UserID,Rolename):returns 0/1
>
>Now I'd like to run a select from all users which have a certain role.
>Why "select * from users, HasRole(users.id,'Admin')" is not working?

Because it can't. You can join a table to a selectable SP, provided the SP
has an output field that can be matched to a key in the table. It is
horribly inefficient, too. I regard it as one of those things you
shouldn't try to do just because you can....

Write an SP to process the users, calling your SP for each and return your
user list directly, along the lines of

create procedure GetUserListForRole (Rolename varchar(31))
returns (id BigInt, username varchar(31), whatever sometype, ....)
as
declare Result smallint;
begin
for select id, username, whatever, .... from users
into :id, :username, :whatever, ...
do
begin
execute procedure HasRole(:id, :Rolename)
returning_values(:Result);
if (Result = 1) then
suspend;
end
end

Then, your application does

select * from GetUserListForRole('Admin')