Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3F3A82E005D for ; Thu, 28 Feb 2008 03:46:10 -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 38069-09 for ; Thu, 28 Feb 2008 03:45:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 557642E006B for ; Thu, 28 Feb 2008 03:46:01 -0400 (AST) Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Thu, 28 Feb 2008 08:45:59 +0100 Message-Id: <47C674BE.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Thu, 28 Feb 2008 08:45:51 +0100 From: "Bart Degryse" To: "=?ISO-8859-1?Q?Professor=20Fl=E1vio=20Brito?=" Cc: Subject: Re: Function returns error (view) References: <6a5e3a6f0802260832k9c1375ap632f8adad0740fa2@mail.gmail.com><6a5 e3a6f0802261020o51327bceof4845c47ae1b099d@mail.gmail.com><47C524E9.A3DD.003 0.0@indicator.be><6a5e3a6f0802271242g65ad1f3cveaf61fe7c5a4d82d@mail.gmail.com> In-Reply-To: <6a5e3a6f0802271242g65ad1f3cveaf61fe7c5a4d82d@mail.gmail.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part3016BCBF.1__=" X-imss-version: 2.050 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/232 X-Sequence-Number: 30323 --=__Part3016BCBF.1__= Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit 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ávio 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= Paul ?? Thanks for your help Flávio 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ávio 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= USER_FOO ?? Thanks for your help Flávio 2008/2/26, Bart Degryse : I think you have a quoting problem You want something like WHERE login= 'Flavo' But you're making something like WHERE login = 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 = t.cod_user AND t.cod_user IN (SELECT cod_user FROM table_user WHERE login= 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ávio 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 execute statement Where is my fault? Thanks Flávio ************************************************************************************************************* 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= '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 = t.cod_user AND t.cod_user IN (SELECT cod_user FROM table_user WHERE login='||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; --=__Part3016BCBF.1__= Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML
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

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 <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 = 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" <prof.flaviobrito@gmail.com> 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 <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.user_time, u.ok
    FROM usuario u, change_user_passwor= d 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" <prof.flaviobrito@gmail.com> 2008-02-26 = 17:32 >>>=20

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;"J= OSE 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?

Than= ks

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_passwo= rd(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;



--=__Part3016BCBF.1__=--