Thread: How to test/read a stored procedure that returns a boolean?
I wrote this function but I'm not sure how to test it in PG Admin III Query. CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE TABLE handles ( handleID_pk serial PRIMARY KEY UNIQUE NOT NULL, userID_fk integer UNIQUE NOT NULL, handle text UNIQUE NOT NULL); CREATE TABLE disallowedHandles ( handle text UNIQUE NOT NULL); create or replace function IsUsedHandle(h text) returns boolean as $$ declare begin select COUNT(*) as num_matches from handles where handles.handle = h; return num_matches > 0; end; $$ LANGUAGE plpgsql; INSERT INTO handles (handle, userid_fk) VALUES ('blah', 0); select * from IsUsedHandle('k'); Instead of true or false, it says ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function "isusedhandle" line 3 at SQL statement If I wanted to call this query and get the boolean result in C++, using PQgetvalue, how would I get this? Thanks!
Kevin Jenkins <gameprogrammer@rakkar.org> writes: > create or replace function IsUsedHandle(h text) returns boolean as $$ > declare > begin > select COUNT(*) as num_matches from handles where handles.handle = h; > return num_matches > 0; > end; > $$ LANGUAGE plpgsql; I think you've confused AS with INTO. You forgot to declare num_matches as a local variable, too. regards, tom lane
Thanks Tom! Also, how do I check if a language is already created so I don't load it twice? "ERROR: language "plpgsql" already exists SQL state: 42710" Here is the code fixed. /* CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE TABLE handles ( handleID_pk serial PRIMARY KEY UNIQUE NOT NULL, userID_fk integer UNIQUE NOT NULL, handle text UNIQUE NOT NULL); CREATE TABLE disallowedHandles ( handle text UNIQUE NOT NULL); */ create or replace function IsUsedHandle(h text) returns boolean as $$ declarenum_matches integer; beginnum_matches := COUNT(*) from handles where handles.handle = h;return num_matches > 0; end; $$ LANGUAGE plpgsql; -- INSERT INTO handles (handle, userid_fk) VALUES ('blah', 0); select * from IsUsedHandle('blah');