Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D42FB2E003D for ; Tue, 26 Feb 2008 12:32:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 61993-02 for ; Tue, 26 Feb 2008 12:32:22 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from gv-out-0910.google.com (gv-out-0910.google.com [216.239.58.184]) by postgresql.org (Postfix) with ESMTP id 9E4972E0032 for ; Tue, 26 Feb 2008 12:32:21 -0400 (AST) Received: by gv-out-0910.google.com with SMTP id y18so855958gvf.7 for ; Tue, 26 Feb 2008 08:32:20 -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:to:subject:mime-version:content-type; bh=mSA2FkBwLhaRvAjkdnYII8FJCzmmw9OUj5Fb8IP5I6w=; b=tw0j2cZAxWfwBl5LNvsY8Z6XEcJUyBLI3RxqAZssz9EnL33xxzZOgKp2rPs6WLVOXS+loc57hsZHMIFwHQVdTybnWNSAumbYzuiJUUHBhma2uIAoBd+HtaaeMBBDSw6mhIvu5SvC6yDknX6/UHJtHvcOEva5FXHgIsFuThHPtqo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=Aay/Y2R56PWkmd/EUQI8nL6iwRGoRDiTlVw66CPFjwFbL2sCsIgKgpNIhVcmfnvxcc9JNHPD5auzFSkUoK+O3RS4pDY9LkkCvHhLfpOONApnT9w8wY/eyq5jZpaUOqiNUUa6QkIyGr99/dQ8jyemO9oErcIeKIMC3GLmVQI3SSQ= Received: by 10.142.172.12 with SMTP id u12mr3929919wfe.95.1204043538287; Tue, 26 Feb 2008 08:32:18 -0800 (PST) Received: by 10.142.131.13 with HTTP; Tue, 26 Feb 2008 08:32:18 -0800 (PST) Message-ID: <6a5e3a6f0802260832k9c1375ap632f8adad0740fa2@mail.gmail.com> Date: Tue, 26 Feb 2008 13:32:18 -0300 From: "=?ISO-8859-1?Q?Professor_Fl=E1vio_Brito?=" To: pgsql-sql@postgresql.org Subject: Function returns error (view) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3379_28124664.1204043538295" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/205 X-Sequence-Number: 30296 ------=_Part_3379_28124664.1204043538295 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 m= y 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-1= 2 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_3379_28124664.1204043538295 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi

Don't know why I can't receive a return like my view fiel= ds (I'm newbie in plpgsql). Postgresql returns me a erro . How can I re= ceived a answer like my view structure?

When I Test my view I receiv= e

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&q= uot;;"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 int= eger,
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_p= assword(USER_FOO varchar(100))
RETURNS SETOF vw_change_password AS
$BODY$
DECLARE
r vw_change_p= assword%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.v= alidate, 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_3379_28124664.1204043538295--