Drop function in Postgres

Question

I have the following function:

CREATE FUNCTION "updateStat"(_request_date timestamp without time zone, _calls integer, _latency integer) RETURNS void AS $$
BEGIN
  LOCK TABLE "statistics" IN SHARE ROW EXCLUSIVE MODE;
  WITH upsert AS (UPDATE "statistics" set calls = calls + _calls, total_latency = total_latency + _latency WHERE request_date=_request_date RETURNING request_date)
  INSERT INTO "statistics" ("request_date", "calls", "total_latency") SELECT _request_date, _calls, _latency WHERE NOT EXISTS (SELECT "request_date" FROM upsert);
END;
$$ LANGUAGE plpgsql;

How can I drop such function?

I have tried the following:

# DROP FUNCTION updateStat(void);
ERROR:  function updatestat(void) does not exist
# DROP FUNCTION updateStat();
ERROR:  function updatestat() does not exist

What do you pass `void` as argument types list ? – Denys Séguret Feb 20 '15 at 14:37 — Denys Séguret, Feb 20 '15 at 14:37
Becouse I though that it needs the return type – lante Feb 20 '15 at 14:38 — lante, Feb 20 '15 at 14:38

score 6 · Accepted Answer · answered Feb 20 '15 at 14:37

6

DROP FUNCTION "updateStat"(timestamp without time zone, integer, integer);

You might want to consider using CREATE OR REPLACE FUNCTION... in your create script, so you can redefine it without needing to drop it each time.

answered Feb 20 '15 at 14:37

cms

5,864
2
28
31

thanks for your reply, but it doesnt work for me :(, this is returning `ERROR: function updatestat(timestamp without time zone, integer, integer) does not exist`. but when I do a `\df` in the CLI this is showing me the function as you describe: [see this image](http://i.imgur.com/XTV3S1S.png) – lante Feb 20 '15 at 14:43
1

@lante even with the quotes ? I left those out of the first edit, sorry. They're necessary to get the mixed case name to work. – cms Feb 20 '15 at 14:45
1

@lante: for details on why you need the quotes, please see the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS In general it's better to avoid quoted identifiers alltogether. – Feb 20 '15 at 15:29
in my case, I created two functions with the same name, one with json param and one with 3 text params. To drop one, in psql (CLI), I typed `DROP FUNCTION my_function(` then I typed _tab_ keyboard key and psql suggest _json_ or _text, text, text_ – bcag2 Mar 18 '22 at 10:24

Drop function in Postgres

1 Answers1