CODEWITHSUNDEEP
postgresqlfunction-call
Erkan Haspulat
Erkan Haspulat

Reputation: 12552

How to call a function, PostgreSQL

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.

Upvotes: 104

Views: 277408

Answers (6)

Krutik
Krutik

Reputation: 1207

We can have two ways of calling the functions written in pgadmin for postgre sql database.

Suppose we have defined the function as below:

CREATE OR REPLACE FUNCTION helloWorld(name text) RETURNS void AS $helloWorld$
DECLARE
BEGIN
    RAISE LOG 'Hello, %', name;
END;
$helloWorld$ LANGUAGE plpgsql;

We can call the function helloworld in one of the following way:

SELECT "helloworld"('myname');

SELECT public.helloworld('myname')

Upvotes: 15

nikolayp
nikolayp

Reputation: 17919

For Postgresql you can use PERFORM. PERFORM is only valid within PL/PgSQL procedure language.

DO $$ BEGIN
    PERFORM "saveUser"(3, 'asd','asd','asd','asd','asd');
END $$;

The suggestion from the postgres team:

HINT: If you want to discard the results of a SELECT, use PERFORM instead.

Upvotes: 47

G.Bouch
G.Bouch

Reputation: 51

I had this same issue while trying to test a very similar function that uses a SELECT statement to decide if a INSERT or an UPDATE should be done. This function was a re-write of a T-SQL stored procedure.
When I tested the function from the query window I got the error "query has no destination for result data". I finally figured out that because I used a SELECT statement inside the function that I could not test the function from the query window until I assigned the results of the SELECT to a local variable using an INTO statement. This fixed the problem.

If the original function in this thread was changed to the following it would work when called from the query window,

$BODY$
DECLARE
   v_temp integer;
BEGIN
SELECT 1 INTO v_temp
FROM "USERS"
WHERE "userID" = $1;

Upvotes: 4

pengli
pengli

Reputation: 79

if your function does not want to return anything you should declare it to "return void" and then you can call it like this "perform functionName(parameter...);"

Upvotes: 7

Milen A. Radev
Milen A. Radev

Reputation: 62563

The function call still should be a valid SQL statement:

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

Upvotes: 141

chburd
chburd

Reputation: 4159

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

Upvotes: -2

Related Questions