[GENERAL] SELECT statement in stored procedure

[Alain Roger]

Hi,

I have the following stored procedure :

CREATE OR REPLACE FUNCTION immense_sp001(IN username VARCHAR, IN
strhash VARCHAR)
RETURNS SETOF accounts LANGUAGE plpgsql
AS '
        DECLARE
        
          Profile_Detected INTEGER :=0;
          act accounts%ROWTYPE;
          rec RECORD;
                
        BEGIN
                
            /* detect if the user logged in exists in database*/
            SELECT count(*) INTO Profile_Detected FROM accounts
              WHERE login=username AND pwd=strhash;
        
            if (Profile_Detected = 1) then
                SELECT INTO act * FROM accounts;
                FOR rec IN select login,status from accounts LOOP
                RETURN NEXT rec;
              END LOOP;
            end if;
            return;
        END;
';


so it should return only 2 fields from my account table (login and status).
however it does not work.

if i replace the line "FOR rec IN select login,status from accounts LOOP" by
FOR rec IN select * from accounts LOOP

it works but i get all fields from my accounts table.

So how can i get only login and status ?

thanks a lot,
Alain

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match