execute funtions in psql

[GeoX]

Hi,

I am new to psql so i am having a little trouble acommodating with the language. Here is the problem: I have created a small functions that returns true or false:

CREATE OR REPLACE FUNCTION DriverLogin(VARCHAR, VARCHAR)
RETURNS BOOLEAN AS '
DECLARE
    l_username ALIAS FOR $1;
    l_password ALIAS FOR $2;
 
     /* for checking the rowcount */
    myRowCount  INTEGER;
 
    /* for getting @@IDENTITY, which PostgreSQL calls an OID */
    theOid   OID;
BEGIN
    select * from tblUsers where usertype=1 and username=l_username and userpassword=l_password;
    GET DIAGNOSTICS myRowCount := ROW_COUNT;
    IF myRowCount = 0 THEN
       return false;
    END IF;
    return true;
END;
' LANGUAGE 'plpgsql';
 

the problem is that i do not know how to call this function from psql or even php. I tried in psql:
PERFORM DriverLogin('a','f'); and it works but it does not show the result returned by the function.

When i tried to call it like this:
SELECT DriverLogin('a','f'); I get this error:
ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "driverlogin" line 11 at SQL statement

So if anyone can please help me I would appreciate it. Thanks!

[bartonc]

You have posted in the Articles section.
Please find the PostgreSQL Forum here.
I'd move your post, but I'm not a moderator of this forum.

[michaelb]

Please read the Posting Guidelines at the top of the Forum, especially the section regarding the use of the CODE tags.

I think a simple SQL like this will suffice:

select count(*) from tblUsers 
where usertype = 1 and username = 'abc' and userpassword = 'xyz';
 

But if you do want to have a function there's no need to have it so bloated, a thin SQL function should do the job:

CREATE OR REPLACE FUNCTION DriverLogin(VARCHAR, VARCHAR)
RETURNS integer AS '
   SELECT COUNT(1)::integer FROM tblUsers  WHERE usertype = 1 AND login = $1 AND passwd = $2;
' LANGUAGE SQL;
 
-- call the function
select DriverLogin('john', 'abc123');