Oprava chyby – přednost mají parametry funkce před názvy sloupců.
authorFrantišek Kučera <franta-hg@frantovo.cz>
Fri, 29 May 2009 21:59:34 +0200
changeset 163461cdf271d0
parent 15 ad391f838b9d
child 17 693f2b2587aa
Oprava chyby – přednost mají parametry funkce před názvy sloupců.
Chybase projevila, když dva uživatelé měli stejné heslo → nebylo možné změnit heslo (pocet = 2).
http://www.abclinuxu.cz/poradna/databaze/show/266816
sql/schéma.sql
     1.1 --- a/sql/schéma.sql	Fri May 29 15:12:02 2009 +0200
     1.2 +++ b/sql/schéma.sql	Fri May 29 21:59:34 2009 +0200
     1.3 @@ -2,7 +2,7 @@
     1.4  -- PostgreSQL database dump
     1.5  --
     1.6  
     1.7 --- Started on 2009-05-10 18:21:07 CEST
     1.8 +-- Started on 2009-05-29 21:13:11 CEST
     1.9  
    1.10  SET client_encoding = 'UTF8';
    1.11  SET standard_conforming_strings = off;
    1.12 @@ -26,14 +26,14 @@
    1.13  -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -
    1.14  --
    1.15  
    1.16 -CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS text
    1.17 +CREATE FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) RETURNS text
    1.18      AS $_$DECLARE pocet integer;
    1.19  BEGIN
    1.20  UPDATE uzivatel
    1.21 - SET heslo = md5($4)
    1.22 - WHERE 	login = $1
    1.23 -	AND domena = $2
    1.24 -	AND heslo = md5($3);
    1.25 + SET heslo = md5("Nove_heslo")
    1.26 + WHERE 	uzivatel.login = "Login"
    1.27 +	AND uzivatel.domena = "Domena"
    1.28 +	AND uzivatel.heslo = md5("Stare_heslo");
    1.29  	GET DIAGNOSTICS pocet = ROW_COUNT;
    1.30  	IF pocet = 1 THEN
    1.31  		RETURN 'Změna hesla pro ' || $1 || '@' || $2 || ' proběhla úspěšně.';
    1.32 @@ -48,10 +48,10 @@
    1.33  --
    1.34  -- TOC entry 1835 (class 0 OID 0)
    1.35  -- Dependencies: 55
    1.36 --- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: -
    1.37 +-- Name: FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying); Type: COMMENT; Schema: posta; Owner: -
    1.38  --
    1.39  
    1.40 -COMMENT ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) IS 'Změní heslo uživatele.';
    1.41 +COMMENT ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) IS 'Změní heslo uživatele.';
    1.42  
    1.43  
    1.44  SET default_tablespace = '';
    1.45 @@ -208,7 +208,7 @@
    1.46  
    1.47  --
    1.48  -- TOC entry 1831 (class 2606 OID 16558)
    1.49 --- Dependencies: 1536 1549 1824
    1.50 +-- Dependencies: 1549 1824 1536
    1.51  -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
    1.52  --
    1.53  
    1.54 @@ -245,11 +245,11 @@
    1.55  -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: -
    1.56  --
    1.57  
    1.58 -REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM PUBLIC;
    1.59 -REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM posta;
    1.60 -GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta;
    1.61 -GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO PUBLIC;
    1.62 -GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta_zmena_hesla;
    1.63 +REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM PUBLIC;
    1.64 +REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM posta;
    1.65 +GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO posta;
    1.66 +GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO PUBLIC;
    1.67 +GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO posta_zmena_hesla;
    1.68  
    1.69  
    1.70  --
    1.71 @@ -335,7 +335,7 @@
    1.72  GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
    1.73  
    1.74  
    1.75 --- Completed on 2009-05-10 18:21:15 CEST
    1.76 +-- Completed on 2009-05-29 21:13:23 CEST
    1.77  
    1.78  --
    1.79  -- PostgreSQL database dump complete