How to run postgresql function (seeing error while running)

Question

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

postgresql.org/docs/current/…

user330315
– user330315

2018-12-10 06:48:14 +00:00
Commented Dec 10, 2018 at 6:48 — user330315
– user330315, Commented Dec 10, 2018 at 6:48

George S · Accepted Answer · 2018-12-10 03:31:20Z

2

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

edited Dec 10, 2018 at 3:31

answered Dec 9, 2018 at 22:32

George S

2,19113 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

harmic · Accepted Answer · 2018-12-10 01:25:29Z

1

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.

answered Dec 10, 2018 at 1:25

harmic

31k6 gold badges75 silver badges102 bronze badges

Collectives™ on Stack Overflow

How to run postgresql function (seeing error while running)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related