From: Professor Flávio Brito <prof.flaviobrito@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Function returns error (view)
Date: Wed, 27 Feb 2008 17:42:23 -0300
Message-ID: <6a5e3a6f0802271242g65ad1f3cveaf61fe7c5a4d82d@mail.gmail.com> (raw)
In-Reply-To: <47C524E9.A3DD.0030.0@indicator.be>
References: <6a5e3a6f0802260832k9c1375ap632f8adad0740fa2@mail.gmail.com>
<6a5e3a6f0802261020o51327bceof4845c47ae1b099d@mail.gmail.com>
<47C524E9.A3DD.0030.0@indicator.be>
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.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ávio 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= USER_FOO ??
>
> Thanks for your help
>
> Flávio
>
> 2008/2/26, Bart Degryse <Bart.Degryse@indicator.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.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 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;
> >
> >
> >
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: prof.flaviobrito@gmail.com
Subject: Re: Function returns error (view)
In-Reply-To: <6a5e3a6f0802271242g65ad1f3cveaf61fe7c5a4d82d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox