How to call a function, PostgreSQL

Question

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.

score 123 · Accepted Answer · answered Dec 23 '09 at 15:21

123

The function call still should be a valid SQL statement:

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

answered Dec 23 '09 at 15:21

Milen A. Radev

60,241
22
105
110

2

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 Dec 23 '09 at 15:28
I tried this with another function which returns void and it works, but what if I wanted to return a boolean ? – Erkan Haspulat Dec 23 '09 at 15:38
2

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 Dec 23 '09 at 15:47
@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 Dec 23 '09 at 16:00
2

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 Dec 23 '09 at 16:23
"...refers to a bug in your function...". That's it, instead of SELECT, I used PERFORM inside my function. Thanks Milen.. – Erkan Haspulat Dec 23 '09 at 17:25

score 40 · Answer 2 · edited Nov 28 '16 at 14:35

40

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.

edited Nov 28 '16 at 14:35

Evandro Coan

8,560
11
83
144

answered Sep 30 '16 at 13:57

itsnikolay

17,415
4
65
64

I want to use SELECT instead of PERFOM inside do begin. If I use SELECT I am facing an error as [42601] ERROR: query has no destination for result data any idea why and I don't want to use INTO some variable because I am using pgtap funcation SELECT has_table('unit_test_output'); it should populate the output on the console – San Jaisy Jun 01 '21 at 14:38

score 11 · Answer 3 · answered Apr 07 '19 at 06:19

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

score 7 · Answer 4 · answered Jul 14 '11 at 02:01

7

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

answered Jul 14 '11 at 02:01

pengli

79
1
1

score 4 · Answer 5 · answered Sep 05 '12 at 14:58

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;

score -3 · Answer 6 · answered Dec 23 '09 at 15:19

-3

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

answered Dec 23 '09 at 15:19

chburd

4,131
28
33

I inserted the line, 'RETURN 1=1' at the bottom of the function definition just to test this, did no good. – Erkan Haspulat Dec 23 '09 at 15:25
1

True, but the problem reported isn't caused by that. – Andrew Aylett Dec 23 '09 at 15:30
Should be a comment (not answering the question) – techkuz Feb 21 '19 at 03:26

How to call a function, PostgreSQL

6 Answers6