Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 059472E2CC4 for ; Thu, 28 Feb 2008 12:09:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41247-02 for ; Thu, 28 Feb 2008 12:09:52 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.175]) by postgresql.org (Postfix) with ESMTP id 709902E00A3 for ; Thu, 28 Feb 2008 12:01:48 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 28so2868436wff.28 for ; Thu, 28 Feb 2008 08:01:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:subject:cc:mime-version:content-type; bh=b3Z8CoPpG9081GhJy/R56MOt7+TYROZyRDcwA4YBGwY=; b=PYUiS2A+nkSfHeBQWaT4JTOITQynzvalkBNOkj9ytd7j2xlOhXVJHmpHEeUEkFfNjjtLnBSs6pKd7Ug+RmQ+2IK+Th5eVtGodVFkj1Oak2r4tyDZ4iyDsDkbmw6qiNCUmeW5+mK/fyBfHCFgx4rboJ9JqBDErN2ARzq33fAXoWo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:subject:cc:mime-version:content-type; b=Zk3r1MM82TamtCJCDvBSWyI7zviDoRjhAZzQWMmrDvMmp8MaGx6S86Lu6SmZM4OJPVzMBkYPGuOxoWECoSGdPrJmYeXLWAgpXHSZ7WEaOlNLmdNAjgr8ei6LCDcPEF/cdxYwU1ApEW+5jmhI4h6EJKrLG0vx3hjelfRu50RBBlA= Received: by 10.142.127.10 with SMTP id z10mr6373430wfc.216.1204214501981; Thu, 28 Feb 2008 08:01:41 -0800 (PST) Received: by 10.142.131.13 with HTTP; Thu, 28 Feb 2008 08:01:41 -0800 (PST) Message-ID: <6a5e3a6f0802280801y7a26ce28n3138eb371fb3cfd8@mail.gmail.com> Date: Thu, 28 Feb 2008 13:01:41 -0300 From: "=?ISO-8859-1?Q?Professor_Fl=E1vio_Brito?=" Subject: Re: Function returns error (view) (RESOLVED) Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14660_13803102.1204214501948" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.97 tagged_above=0 required=5 tests=AWL=-0.729, HTML_MESSAGE=0.001, MISSING_HEADERS=1.581, RCVD_IN_SORBS_WEB=1.117 X-Spam-Level: * X-Archive-Number: 200802/233 X-Sequence-Number: 30324 ------=_Part_14660_13803102.1204214501948 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi I discovered that when a person did not change the password, there is no information into change_user_password table, then a exception raise but wasn't treated. Now it is OK. Thanks for all CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100)) RETURNS SETOF vw_change_password AS $BODY$ DECLARE r vw_change_password%ROWTYPE; USER_FOO alias for $1; sql TEXT; BEGIN sql=3D 'SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end, t.validate, t.date_add, t.user_time, u.ok FROM table_user u, change_user_password t WHERE u.cod_user =3D t.cod_user AND t.cod_user IN (SELECT cod_user FROM table_user WHERE login=3D'|| quote_literal(USER_FOO) ||')'; FOR r IN EXECUTE sql LOOP RETURN NEXT r; END LOOP; IF NOT FOUND THEN RAISE EXCEPTION 'User not found in change_user_password'; END IF; RETURN; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; 2008/2/28, Bart Degryse : > > Please send the complete DDL for your function and the tables it uses. > Also inform us of the database version you're using. > > >>> "Professor Fl=E1vio Brito" 2008-02-27 21= :42 > >>> > > Hi > > After I did it I received it > > SELECT * FROM search_password('Paul'); > > > > > ERROR: set-valued function called in context that cannot accept a set > SQL state: 0A000 > Context: PL/pgSQL function "search_password(" line 14 at return next > > Error at WHERE login=3D Paul ?? > > Thanks for your help > > Fl=E1vio > > 2008/2/27, Bart Degryse : > > > > How do you call your function? You should call it like this: > > SELECT * FROM seach_password('Flavio'); > > > > Replace Flavio with the login of someone in table_user. > > Also watch out for the function name: if you copied my suggestion it is > > seach_... and not search_... > > > > I would also suggest you replace the > > ...t.cod_user IN (subselect) > > by a join construction. I think it's more performant. > > > > > > >>> "Professor Fl=E1vio Brito" 2008-02-26 > > 19:20 >>> > > Hi > > > > After I did it I received it > > > > ERROR: set-valued function called in context that cannot accept a set > > SQL state: 0A000 > > Context: PL/pgSQL function "seach_password(" line 14 at return next > > > > Error at WHERE login=3D USER_FOO ?? > > > > Thanks for your help > > > > Fl=E1vio > > > > 2008/2/26, Bart Degryse : > > > > > > I think you have a quoting problem > > > You want something like > > > WHERE login=3D 'Flavo' > > > But you're making something like > > > WHERE login =3D Flavo > > > > > > Something like this should work... > > > CREATE OR REPLACE FUNCTION seach_password(USER_FOO > > > IN table_user.login%TYPE) > > > RETURNS SETOF vw_change_password AS > > > $BODY$ > > > DECLARE > > > r vw_change_password%ROWTYPE; > > > USER_FOO alias for $1; > > > BEGIN > > > FOR r IN ( > > > SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end= , > > > t.validate, t.date_add, t.user_time, u.ok > > > FROM usuario u, change_user_password t > > > WHERE u.cod_user =3D t.cod_user AND t.cod_user IN (SELECT > > > cod_user FROM table_user WHERE login=3D USER_FOO)) > > > LOOP > > > RETURN NEXT r; > > > END LOOP; > > > IF NOT FOUND THEN > > > RAISE EXCEPTION 'USER not found (%)', USER_FOO; > > > END IF; > > > RETURN; > > > END > > > $BODY$ > > > LANGUAGE 'plpgsql' VOLATILE; > > > > > > > > > >>> "Professor Fl=E1vio Brito" 2008-02-2= 6 > > > 17:32 >>> > > > Hi > > > > > > Don't know why I can't receive a return like my view fields (I'm > > > newbie in plpgsql). Postgresql returns me a erro . How can I received= a > > > answer like my view structure? > > > > > > When I Test my view I receive > > > > > > SELECT seach_password('user_login_foo') > > > > > > My view returns me > > > > > > 25746;"MARCELO > > > ";"bio1";"bio1";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12 12:51: > > > 40.229282";"TRUE" > > > 30356;"JOSE DE JESUS > > > ";"977";"377";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12 12:52: > > > 19.688381";"TRUE" > > > > > > It's OK > > > > > > but when I use function it returns me > > > > > > ERROR: column "user_login_foo" does not exist > > > SQL state: 42703 > > > Context: PL/pgSQL function "search_password" line 14 at for over > > > execute statement > > > > > > Where is my fault? > > > > > > Thanks > > > > > > Fl=E1vio > > > > > > > > > *********************************************************************= **************************************** > > > vw_change_password attributes > > > > > > cod_user integer, > > > user_name varchar(150), > > > openpsw varchar (32), > > > user_password varchar (50), > > > end timestamp, > > > validate boolean, > > > date_add timestamp, > > > user_time timestamp, > > > ok boolean > > > > > > > > > CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100)) > > > RETURNS SETOF vw_change_password AS > > > $BODY$ > > > DECLARE > > > r vw_change_password%ROWTYPE; > > > USER_FOO alias for $1; > > > sql TEXT; > > > BEGIN > > > sql=3D 'SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, > > > t.end, t.validate, t.date_add, t.user_time, u.ok > > > FROM usuario u, change_user_password t > > > WHERE u.cod_user =3D t.cod_user AND t.cod_user > > > IN > > > (SELECT cod_user > > > FROM table_user > > > WHERE login=3D'||USER_FOO||')'; > > > > > > FOR r IN EXECUTE sql > > > LOOP > > > RETURN NEXT r; > > > END LOOP; > > > IF NOT FOUND THEN > > > RAISE EXCEPTION 'USER not found', USER_FOO; > > > END IF; > > > RETURN; > > > END > > > $BODY$ > > > LANGUAGE 'plpgsql' VOLATILE; > > > > > > > > > > > > ------=_Part_14660_13803102.1204214501948 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi

I discovered that when a person did not change the password, ther= e is no information into change_user_password  table, then a exception= raise but wasn't  treated. Now it is OK.

Thanks for all

CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100))
RETURNS= SETOF vw_change_password AS
$BODY$
DECLARE
r vw_change_password%R= OWTYPE;
USER_FOO alias for $1;
sql TEXT;
BEGIN
sql=3D 'SELE= CT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end, t.validate, = t.date_add, t.user_time, u.ok
   FROM table_user u, change_user_password t
  WHERE u.co= d_user =3D t.cod_user  AND t.cod_user
    IN
&nbs= p;   (SELECT cod_user
      &nbs= p; FROM table_user
         = ;   WHERE login=3D'|| quot= e_literal(USER_FOO) ||')';

FOR r IN EXECUTE sql
 LOOP
 RETURN NEXT r;
END LOOP;=
 IF NOT FOUND THEN
        R= AISE EXCEPTION 'User not found in ch= ange_user_password';
 END IF;
 RETURN;
 END
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;


2008/2/28, Bart Degryse <<= a href=3D"mailto:Bart.Degryse@indicator.be">Bart.Degryse@indicator.be&g= t;:
Please send the complete DDL for your function and the tables it = uses.
Also inform us of the database version you're using.

>&g= t;> "Professor Fl=E1vio Brito" <prof.flaviobrito@gmail.com> 2008-02-27 21:42 >= >>


Hi
<= br>After I did it I received it

SELECT * FROM search_password('Paul');


 

ERROR: set-valued function called in context t= hat cannot accept a set
SQL state: 0A000
Context: PL/pgSQL function &= quot;search_password(" line 14 at return next

Error at WHERE lo= gin=3D Paul ??

Thanks for your help

Fl=E1vio

2008/2/27, Bart Degryse <Bart.Degryse@indicator.be>:
How do you call your function? You should call it like this:
SELECT * FROM seach_password('Flavio');
 
Replace Flavio with the login of someone in table_user.
Also watch out for the function name: if you copied my suggestion it i= s seach_... and not search_...
 
I would also suggest you replace the
...t.cod_user IN (subselect)
by a join construction. I think it's more performant.


>>> "Professor Fl=E1vio Brito" <prof.flaviobrito@gmail.com> 2= 008-02-26 19:20 >>>

Hi

After I did it I received it

ERROR: set-va= lued function called in context that cannot accept a set
SQL state: 0A00= 0
Context: PL/pgSQL function "seach_password(" line 14 at retu= rn next

Error at WHERE login=3D USER_FOO ??

Thanks for your help

= Fl=E1vio

2008/2/26, Bart Degryse <Bart.Degryse@indicator.be>:=20
I think you have a quoting problem
You want something like
  WHERE login=3D 'Flavo'
But you're making something like
  WHERE login =3D Flavo
 
Something like this should work...
CREATE OR REPLACE FUNCTION seach_password(USER_FOO IN table_user.= login%TYPE)
RETURNS SETOF vw_change_password AS
$BODY$
DECL= ARE
r vw_change_password%ROWTYPE;
USER_FOO alias for $1;
B= EGIN
  FOR r IN (
    SELECT u.cod_user, = u.user_name, u.openpsw, t.user_password, t.end, t.validate, t.date_add, t.u= ser_time, u.ok
    FROM usuario u, change_user_password t=
    WHERE u.cod_user =3D t.cod_user  AND t.c= od_user IN (SELECT cod_user FROM table_user WHERE login=3D U= SER_FOO))
  LOOP
    RETURN NEXT r;
  END LOOP;  IF NOT FOUND THEN
    RAISE EXCEPTION '= USER not found (%)', USER_FOO;
  END IF;
  RETURN;
E= ND
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


>>> "Professor Fl=E1vio Brito" <prof.flaviobrito@gmail.com> 2= 008-02-26 17:32 >>>=20

Hi

Don't know why I can't receive a return li= ke my view fields (I'm newbie in plpgsql). Postgresql returns me a erro= . How can I received a answer like my view structure?

When I Test m= y view I receive

SELECT  seach_password('user_login_foo')

My view re= turns me

25746;"MARCELO ";"bio1"= ;;"bio1";"2008-02-19";"FALSE";"2008-02-1= 2";"2008-02-12 12:51:40.229282";"TRUE"
30356;"JOSE DE JESUS ";"977";"377";"2008= -02-19";"FALSE";"2008-02-12";"2008-02-12 12:5= 2:19.688381";"TRUE"

It's OK

but whe= n I use function it returns me

ERROR: column "user_login_foo" does not exist
SQL state: 4= 2703
Context: PL/pgSQL function "search_password" line 14 at f= or over execute statement

Where is my fault?

Thanks

Fl=E1vio

**********************************************************= ***************************************************
vw_change_password&n= bsp; attributes

cod_user integer,
user_name varchar(150),
open= psw varchar (32),
user_password varchar (50),
end timestamp,
validate boolean,
date_= add timestamp,
user_time timestamp,
ok boolean


CREATE OR R= EPLACE FUNCTION seach_password(USER_FOO varchar(100))
RETURNS SETOF vw_c= hange_password AS
$BODY$
DECLARE
r vw_change_password%ROWTYPE;
USER_FOO alias for $= 1;
sql TEXT;
BEGIN
sql=3D 'SELECT u.cod_user, u.user_name, u.o= penpsw, t.user_password, t.end, t.validate, t.date_add, t.user_time, u.ok    FROM usuario u, change_user_password t
  WHERE u.cod_u= ser =3D t.cod_user  AND t.cod_user
    IN
 = ;   (SELECT cod_user
        FR= OM table_user
            = WHERE login=3D'||USER_FOO||')';

FOR r IN EXECUTE sql
 LOOP
 RETURN NEXT r;
END LOOP;
=  IF NOT FOUND THEN
        RAISE= EXCEPTION 'USER not found', USER_FOO;
 END IF;
 RE= TURN;
 END
 $BODY$
  LANGUAGE 'plpgsql' VOL= ATILE;



=


------=_Part_14660_13803102.1204214501948--