views:

330

answers:

2

I'm trying to use a function with PostgreSQL to save some data. Here is the create script:

-- Function: "saveUser"(integer, character varying, character varying, character varying, character varying, character varying)

-- DROP FUNCTION "saveUser"(integer, character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION "saveUser"("pUserID" integer, "pName" character varying, "pLastName" character varying, "pUserName" character varying, "pPassword" character varying, "peMail" character varying)
  RETURNS boolean AS
$BODY$
BEGIN
SELECT 1
FROM "USERS"
WHERE "userID" = $1;

IF FOUND THEN
UPDATE "USERS" 
    SET  "name" = $2,
 "lastName" = $3,
 "userName" = $4,
 "password" = $5,
 "eMail" = $6
WHERE "userID" = $1;
ELSE
    INSERT INTO "USERS"
    ("name", "lastName", "userName", "password", "eMail")
    VALUES
        ($2, $3, $4, $5, $6);
END IF;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION "saveUser"(integer, character varying, character varying, character varying, character varying, character varying) OWNER TO postgres;

PostreSQL Documentation states that to call a function which does not return any resultset, it is sufficient to write only its name and properties. So I try to call the function like this:

"saveUser"(3, 'asd','asd','asd','asd','asd');

But I get the error below:

ERROR:  syntax error at or near ""saveUser""
LINE 1: "saveUser"(3, 'asd','asd','asd','asd','asd')
     ^

********** Error **********

ERROR: syntax error at or near ""saveUser""
SQL state: 42601
Character: 1

I have other functions which return a resultset. I use SELECT * FROM "fnc"(...) to call them and it works. Why am I getting this error?


EDIT: I am using pgAdmin III Query tool and trying to execute the SQL Statements there.

+1  A: 

you declare your function as returning boolean, but it never returns anything.

chburd
I inserted the line, 'RETURN 1=1' at the bottom of the function definition just to test this, did no good.
Erkan Haspulat
True, but the problem reported isn't caused by that.
Andrew Aylett
+2  A: 

The function call still should be a valid SQL statement:

SELECT "saveUser"(3, 'asd','asd','asd','asd','asd');
Milen A. Radev
Using this, I get the error; "ERROR: query has no destination for result data". I wonder if this error has something to do with me wanting to return a boolean?
Erkan Haspulat
I tried this with another function which returns void and it works, but what if I wanted to return a boolean ?
Erkan Haspulat
Erkan: Tried combining the two answers? I.e. your invocation is wrong, and you're not returning anything for a function that is expected to return bool.Moreover, why are you having mixed-case identifiers that you have to quote all the time?
Alex Brasetvik
@Alex: I declared the function to return boolean, and inserted 'RETURN 1=1;' at the bottom of the function, then used 'SELECT "saveUser"(..)' and it didn't work. I also tried to return void, remove 'RETURN 1=1', and use the same SELECT statement above, didn't work either.
Erkan Haspulat
The new error messaage you see ("ERROR: query has no destination for result data") refers to a bug in your function - the "SELECT" SQL statements in PL/pgSQL should always be "SELECT INTO". If you don't need the result use "PERFORM" (http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT).
Milen A. Radev
"...refers to a bug in your function...". That's it, instead of SELECT, I used PERFORM inside my function. Thanks Milen..
Erkan Haspulat