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:
[CODE=SQL]
CREATE OR REPLACE FUNCTION DriverLogin(VAR CHAR, 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_user name and userpassword=l_ password;
GET DIAGNOSTICS myRowCount := ROW_COUNT;
IF myRowCount = 0 THEN
return false;
END IF;
return true;
END;
' LANGUAGE 'plpgsql';
[/CODE]
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 "driverlogi n" 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:
[CODE=sql]
select count(*) from tblUsers
where usertype = 1 and username = 'abc' and userpassword = 'xyz';
[/CODE]

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:
[CODE=sql]
CREATE OR REPLACE FUNCTION DriverLogin(VAR CHAR, VARCHAR)
RETURNS integer AS '
SELECT COUNT(1)::integ er FROM tblUsers WHERE usertype = 1 AND login = $1 AND passwd = $2;
' LANGUAGE SQL;

-- call the function
select DriverLogin('jo hn', 'abc123');
[/CODE]