How to call a function, PostgreSQL

PostgresqlFunction Call

Postgresql Problem Overview


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.

Postgresql Solutions


Solution 1 - Postgresql

The function call still should be a valid SQL statement:

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

Solution 2 - Postgresql

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.

Solution 3 - Postgresql

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...);"

Solution 4 - Postgresql

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')

Solution 5 - Postgresql

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;

Solution 6 - Postgresql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionErkan HaspulatView Question on Stackoverflow
Solution 1 - PostgresqlMilen A. RadevView Answer on Stackoverflow
Solution 2 - PostgresqlitsnikolayView Answer on Stackoverflow
Solution 3 - PostgresqlpengliView Answer on Stackoverflow
Solution 4 - PostgresqlKrutikView Answer on Stackoverflow
Solution 5 - PostgresqlG.BouchView Answer on Stackoverflow
Solution 6 - PostgresqlchburdView Answer on Stackoverflow