CODEWITHSUNDEEP
postgresqlplpgsql
Kevv Keka
Kevv Keka

Reputation: 314

How to run postgresql function (seeing error while running)

I created Postgresql function like this. And tried different ways to run it as mentioned in this question. None of the works. 

CREATE OR REPLACE FUNCTION func_update_count(
  updateType text,
  userid     text
)
  RETURNS integer AS $$
DECLARE
    _qry char(1024);
    l_rows integer;
BEGIN
    RAISE NOTICE 'Running ++';
    IF updateType = '+' THEN
        RAISE NOTICE 'Running ++';
        UPDATE user_account SET tptcount = tptcount + 1 WHERE useridcode = '@userid';
    ELSIF updateTYpe = '-' THEN
        RAISE NOTICE 'Running --';
        UPDATE user_account SET tptcount = tptcount - 1 WHERE useridcode = '@userid';
    ELSE
        RAISE NOTICE 'Not running any';
    END IF;

    GET DIAGNOSTICS l_rows = ROW_COUNT; 
    RETURN l_rows;  
END;
$$
LANGUAGE 'plpgsql';

Tried to execute function like this:

Runs successfully:

UPDATE user_account SET tptcount = tptcount + 1 WHERE useridcode = 'chris_32';

=====

Seeing error: ERROR: prepared statement "func_update_count" does not exist for below query.

EXECUTE func_update_count("+", "chris_32"); -- Does not work

=====

Seeing error: ERROR: column "+" does not exist LINE 1: SELECT func_update_count("+", "chris_32"); -- Does not work for below query

SELECT func_update_count("+", "chris_32"); -- Does not work

Upvotes: 0

Views: 198

Answers (2)

George S
George S

Reputation: 2161

In PostgreSQL, the values within double quotes are interpreted as references to columns rather than string literals. Thus:

SELECT func_update_count("+", "chris_32"); 
 --pulls values from the column named + and column named chris_32

Should probably be:

SELECT func_update_count('+', 'chris_32');

Upvotes: 2

harmic
harmic

Reputation: 30597

Seeing error: ERROR: prepared statement "func_update_count" does not exist for below query.

EXECUTE is used for executing prepared statements, not running postgresql functions. See EXECUTE in the manual.

You would execute a function by calling it from a query (as per your second attempt).

Seeing error: ERROR: column "+" does not exist

Double quotes " are used to quote identifiers if they have spaces in them (eg. "my column"), or to preserve case (eg. "MyColumn").

See Syntax for Identifiers and Keywords.

Single quotes (') can be used for string literals, as well as other styles - see Constants in the manual.

Upvotes: 1

Related Questions