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   quot saveUser quot  integer  character varying  character varying  character varying  character varying  character varying   -- DROP FUNCTION  quot saveUser quot  integer  character varying  character varying  character varying  character varying  character varying    CREATE OR REPLACE FUNCTION  quot saveUser quot   quot pUserID quot  integer   quot pName quot  character varying   quot pLastName quot  character varying   quot pUserName quot  character varying    quot pPassword quot  character varying   quot peMail quot  character varying  RETURNS boolean AS  BODY  BEGIN SELECT 1 FROM  quot USERS quot  WHERE  quot userID quot     1   IF FOUND THEN UPDATE  quot USERS quot       SET      quot name quot     2       quot lastName quot     3       quot userName quot     4       quot password quot     5       quot eMail quot     6 WHERE  quot userID quot     1  ELSE     INSERT INTO  quot USERS quot        quot name quot    quot lastName quot    quot userName quot    quot password quot    quot eMail quot       VALUES           2   3   4   5   6   END IF  END  BODY    LANGUAGE  plpgsql  VOLATILE   COST 100  ALTER FUNCTION  quot saveUser quot  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   quot saveUser quot  3   asd   asd   asd   asd   asd     But I get the error below  ERROR   syntax error at or near  quot  quot saveUser quot  quot  LINE 1   quot saveUser quot  3   asd   asd   asd   asd   asd                      Error             ERROR  syntax error at or near  quot  quot saveUser quot  quot  SQL state  42601 Character  1  I have other functions which return a resultset  I use SELECT   FROM  quot fnc quot       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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The function call still should be a valid SQL statement   SELECT  saveUser  3   asd   asd   asd   asd   asd

User · Answer

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

User · Answer

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

[postgresql] How to call a function, PostgreSQL

Examples related to postgresql

Examples related to function-call