Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CDAD02E0048 for ; Wed, 27 Feb 2008 16:42:27 -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 09331-06 for ; Wed, 27 Feb 2008 16:42:24 -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.168]) by postgresql.org (Postfix) with ESMTP id 4D8E92E0045 for ; Wed, 27 Feb 2008 16:42:24 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 28so2278326wff.28 for ; Wed, 27 Feb 2008 12:42:23 -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=edBADCx3WPdc2qZiNrNveGoZvgGGIMte0xG929Tnd6g=; b=JW2Er35NIUfsp7EISReylw+eWrKNmQHoz9/JweUtMplBm71xn1USj04+ZXwtOrp8qWTg75u2O5QE5OUNfs89zVKwXFBfm/IRu6C8iGDfylD3f4EhXpi6xruILwluLAckYo21Mgs+bxHuMPuX0rje60FknUICsiKdLT0ej6DPTAQ= 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=w7EMhZvSsP8O2Vm74J4ni0h2J7UFVfkqyUpmJjW7ayK+DCtMIJECTICmIagRJlB1SIw9u4DYNUQEYEqyAMxHjcc0JU2gdFAwLMJNlp9I7exGFf6krf6TxWKlIm5g9qhTxUQb9g+AxijkScUQRbWo51kN2zInOKhVhuSmZm714F8= Received: by 10.142.241.10 with SMTP id o10mr5742527wfh.217.1204144943217; Wed, 27 Feb 2008 12:42:23 -0800 (PST) Received: by 10.142.131.13 with HTTP; Wed, 27 Feb 2008 12:42:23 -0800 (PST) Message-ID: <6a5e3a6f0802271242g65ad1f3cveaf61fe7c5a4d82d@mail.gmail.com> Date: Wed, 27 Feb 2008 17:42:23 -0300 From: "=?ISO-8859-1?Q?Professor_Fl=E1vio_Brito?=" Subject: Re: Function returns error (view) Cc: pgsql-sql@postgresql.org In-Reply-To: <47C524E9.A3DD.0030.0@indicator.be> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10036_3885504.1204144943217" References: <6a5e3a6f0802260832k9c1375ap632f8adad0740fa2@mail.gmail.com> <6a5e3a6f0802261020o51327bceof4845c47ae1b099d@mail.gmail.com> <47C524E9.A3DD.0030.0@indicator.be> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.745 tagged_above=0 required=5 tests=AWL=-0.954, HTML_MESSAGE=0.001, MISSING_HEADERS=1.581, RCVD_IN_SORBS_WEB=1.117 X-Spam-Level: * X-Archive-Number: 200802/225 X-Sequence-Number: 30316 ------=_Part_10036_3885504.1204144943217 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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-26 > > 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 execut= e > > 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.e= nd, > > 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_10036_3885504.1204144943217 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi

After I did it I received it

SELEC= T * 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 login=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-valued function called in context that can= not accept a set
SQL state: 0A000
Context: PL/pgSQL function "se= ach_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>:
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 >>>

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_10036_3885504.1204144943217--