Function returns error (view) (RESOLVED)

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= '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 = t.cod_user AND t.cod_user
IN
(SELECT cod_user
FROM table_user
WHERE login='|| 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 <Bart(dot)Degryse(at)indicator(dot)be>:
>
> 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" <prof(dot)flaviobrito(at)gmail(dot)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= Paul ??
>
> Thanks for your help
>
> Flávio
>
> 2008/2/27, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)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ávio Brito" <prof(dot)flaviobrito(at)gmail(dot)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= USER_FOO ??
> >
> > Thanks for your help
> >
> > Flávio
> >
> > 2008/2/26, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be>:
> > >
> > > 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" <prof(dot)flaviobrito(at)gmail(dot)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 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;
> > >
> > >
> > >
> >
>