"Function does not exist" error when creating the function with more parameters

Question

I have dropped a PL/pgSQL function and I try to recreate it with one more parameter (with default value). But I get a strange error:

ERROR: function vytvor_kod_sj(text, integer, integer) does not exist
SQL state: 42883

I would expect such an error while dropping or calling the function, not while creating it. I made sure that the error occurs exactly while trying to create it, not in any of the other functions or triggers I created from the same sql file.

I made a dummy function without the last parameter and it works now, but it is definitely not what I want - not only I don't need the function without the last parameter anymore, I usually call the function only with the first two or three parameter, so I need to drop it just after creating the new version of my function to avoid mismatches. Fortunately, there are no errors while doing this, but it is hardly optimal solution.

So does anyone know how to solve this mysterious problem?

I have PostgreSQL 9.3.4 on Windows 32; I use pgAdmin 1.18.1

My code:

CREATE OR REPLACE FUNCTION vytvor_kod_sj( _akce text, typ_sj integer, 
    podtyp integer DEFAULT 0, styl integer DEFAULT NULL ) 
RETURNS text AS $$
DECLARE
  --styl integer;
  _nazev_seq text;
  _min integer;
  _max integer;
  _nazev text;
  _soucasna integer;
BEGIN
  IF styl IS NULL THEN
    SELECT nomenklatura INTO styl FROM akce WHERE kod_akce = _akce;
  END IF;

  IF NOT EXISTS( SELECT id_nom FROM pro_nom_pravidlo_sj 
      WHERE id_nom = styl AND sj = typ_sj AND typ = podtyp ) THEN
    IF podtyp = 0 OR NOT EXISTS( SELECT id_nom FROM pro_nom_pravidlo_sj 
        WHERE id_nom = styl AND sj = typ_sj AND typ = 0 ) THEN
      RAISE NOTICE 'Pro daný typ stratigrafické jednotky není vytvořeno žádné pravidlo!';
      RETURN NULL;
    ELSE
      podtyp := 0;
    END IF;
  END IF;

  _nazev_seq := _akce || '_' || typ_sj || '_' || podtyp || '_seq';
  IF NOT EXISTS (SELECT 0 FROM pg_class where relname = _nazev_seq ) THEN
    SELECT min, max INTO _min, _max FROM pro_nom_pravidlo_sj 
      WHERE id_nom = styl AND sj = typ_sj AND typ = podtyp;   
    IF _max IS NOT NULL THEN
      EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s ', _nazev_seq, _min, _max);
    ELSE
      EXECUTE format('CREATE SEQUENCE %I MINVALUE %s ', _nazev_seq, _min);
    END IF;
  END IF;

  --IF strict IS TRUE THEN  
    RETURN (
      SELECT predpona FROM pro_nom_pravidlo_sj 
      WHERE id_nom = styl AND sj = typ_sj AND typ = podtyp
    ) || CAST(nextval(_nazev_seq) AS TEXT);
  --END IF;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;
ALTER FUNCTION vytvor_kod_sj( text, integer, integer ) OWNER TO ins_daemon;

The old version had styl variable declared, not as a parameter, and there was no test whether it is null. Otherwise I didn't change anything.

A question well presented, even if it turned out to be an oversight. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 12, 2014 at 2:44

Pavel V. · Accepted Answer · 2014-07-10 08:11:15Z

1

I noticed the cause of the problem just after posting the question:

ALTER FUNCTION vytvor_kod_sj( text, integer, integer ) OWNER TO ins_daemon;

It tries to alter the old function.

answered Jul 10, 2014 at 8:11

Pavel V.

2,87010 gold badges50 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

"Function does not exist" error when creating the function with more parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related