Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A5B722E00AC for ; Tue, 26 Feb 2008 14:23:13 -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 05040-01-6 for ; Tue, 26 Feb 2008 14:23:10 -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.169]) by postgresql.org (Postfix) with ESMTP id 465BC2E00F6 for ; Tue, 26 Feb 2008 14:20:40 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 28so1452854wff.28 for ; Tue, 26 Feb 2008 10:20:39 -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:in-reply-to:mime-version:content-type:references; bh=SP4giYspiDVaUdfFqfYN8inCOPduasvLm9HvQ89QCTc=; b=MmBVpSiIqcCgx1KokhhG+AhGeiUJWeSOQPokjmtjFFDQwFr4PiyBnfEoTzKmfT7VPITu7CC1/JSZJcYwjd4uKZ3QnGB8abyCGF8LXS+vyFtCw8h/6i/WCqPHZDJKj2SkWzuNU8ur7gqa0d+y/93BzJH28UJ1WDLM/jao6ArQwyE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=rAN8JtRc4d/qGiFgU6G+37VYnvVjU4f3An1wr08jxZZx9HSVYZSqR1RVrRYv8u9Zr/L9w01RYErbOkht6/tvS/+iM7bPaKDHZr5kCMLELSie11Y8ZTBzaqvWRpkzoXifsUIdj613Rvhg4kWCg/QlFrPCEPJn4oVmhDFwKJTvBJc= Received: by 10.142.103.6 with SMTP id a6mr4079710wfc.21.1204050039331; Tue, 26 Feb 2008 10:20:39 -0800 (PST) Received: by 10.142.131.13 with HTTP; Tue, 26 Feb 2008 10:20:39 -0800 (PST) Message-ID: <6a5e3a6f0802261020o51327bceof4845c47ae1b099d@mail.gmail.com> Date: Tue, 26 Feb 2008 15:20:39 -0300 From: "=?ISO-8859-1?Q?Professor_Fl=E1vio_Brito?=" Subject: Re: Function returns error (view) Cc: pgsql-sql@postgresql.org In-Reply-To: <47C4539D.A3DD.0030.0@indicator.be> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3948_15175778.1204050039302" References: <6a5e3a6f0802260832k9c1375ap632f8adad0740fa2@mail.gmail.com> <47C4539D.A3DD.0030.0@indicator.be> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.791 tagged_above=0 required=5 tests=AWL=-0.790, HTML_MESSAGE=0.001, MISSING_HEADERS=1.581 X-Spam-Level: X-Archive-Number: 200802/213 X-Sequence-Number: 30304 ------=_Part_3948_15175778.1204050039302 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 F= ROM > 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-26 17= :32 > >>> > Hi > > Don't know why I can't receive a return like my view fields (I'm newbie i= n > 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_3948_15175778.1204050039302 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi

After I did it I received it

ERROR: set-valued function c= alled in context that cannot accept a set
SQL state: 0A000
Context: P= L/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 <= Bart.Degryse@indicator.be&= gt;:
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
$B= ODY$
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.d= ate_add, t.user_time, u.ok
    FROM usuario u, change_use= r_password t
    WHERE u.cod_user =3D t.cod_user&n= bsp; AND t.cod_user IN (SELECT cod_user FROM table_user WHER= E login=3D USER_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" <<= a href=3D"mailto:prof.flaviobrito@gmail.com" target=3D"_blank" onclick=3D"r= eturn top.js.OpenExtLink(window,event,this)">prof.flaviobrito@gmail.com= > 2008-02-26 17:32 >>>

Hi

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

When I Test my view I receive

SELECT  seach_password('= user_login_foo')

My view returns me

2574= 6;"MARCELO ";"bio1";"bio1";"2008-02-19&q= uot;;"FALSE";"2008-02-12";"2008-02-12 12:51:40.229= 282";"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_3948_15175778.1204050039302--