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!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Looks to me like you are declaring "begin.... "

I don't see ythat you need to declare anything in your SP... Also -- it is
wrong in postgreSQL to create "C-Style" functions declarations -- you only
list the "type" of parameter... parameters are mapped internally as $1, $2,
$3, etctera -- looking at

IsUsedHandle(h text)

sit SHOUD BE:

IsUsedHandle(text)

I would write it like this:

CREATE OR REPLACE FUNCTION sys_IsUsedHandle(text) RETURNS boolean AS
$BODY$

DECLARE a INTEGER;
DECLARE b BOOLEAN;

BEGIN

SELECT CASE WHEN (SELECT COUNT(*) FROM handles h WHERE h.handle = $1) > 0
THEN true ELSE false INTO B;
RETURN B;

END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;




"Kevin Jenkins" <gameprogrammer@rakkar.org> wrote in message
news:478E901D.7060701@rakkar.org...
>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!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

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 $$
declare
num_matches integer;
begin
num_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');

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq