By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,956 Members | 1,722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,956 IT Pros & Developers. It's quick & easy.

execute funtions in psql

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION DriverLogin(VARCHAR, VARCHAR)
  2. RETURNS BOOLEAN AS '
  3. DECLARE
  4.     l_username ALIAS FOR $1;
  5.     l_password ALIAS FOR $2;
  6.  
  7.      /* for checking the rowcount */
  8.     myRowCount  INTEGER;
  9.  
  10.     /* for getting @@IDENTITY, which PostgreSQL calls an OID */
  11.     theOid   OID;
  12. BEGIN
  13.     select * from tblUsers where usertype=1 and username=l_username and userpassword=l_password;
  14.     GET DIAGNOSTICS myRowCount := ROW_COUNT;
  15.     IF myRowCount = 0 THEN
  16.        return false;
  17.     END IF;
  18.     return true;
  19. END;
  20. ' LANGUAGE 'plpgsql';
  21.  
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!
May 25 '07 #1
Share this Question
Share on Google+
2 Replies


bartonc
Expert 5K+
P: 6,596
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.
May 25 '07 #2

Expert 100+
P: 534
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:
Expand|Select|Wrap|Line Numbers
  1. select count(*) from tblUsers 
  2. where usertype = 1 and username = 'abc' and userpassword = 'xyz';
  3.  
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION DriverLogin(VARCHAR, VARCHAR)
  2. RETURNS integer AS '
  3.    SELECT COUNT(1)::integer FROM tblUsers  WHERE usertype = 1 AND login = $1 AND passwd = $2;
  4. ' LANGUAGE SQL;
  5.  
  6. -- call the function
  7. select DriverLogin('john', 'abc123');
  8.  
May 27 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.