pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction returns error (view)
11+ messages / 4 participants
[nested] [flat]
* Function returns error (view)
@ 2008-02-26 16:32 Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:36 ` Re: Function returns error (view) Colin Wetherbee <cww@denterprises.org>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 2 replies; 11+ messages in thread
From: Professor Flávio Brito @ 2008-02-26 16:32 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
@ 2008-02-26 16:36 ` Colin Wetherbee <cww@denterprises.org>
2008-02-26 16:48 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Colin Wetherbee @ 2008-02-26 16:36 UTC (permalink / raw)
To: Professor Flávio Brito <prof.flaviobrito@gmail.com>; +Cc: pgsql-sql
Professor Flávio Brito wrote:
> When I Test my view I receive
>
> SELECT seach_password('user_login_foo')
[...]
> ERROR: column "user_login_foo" does not exist
> SQL state: 42703
> Context: PL/pgSQL function "search_password" line 14 at for over execute
> statement
seach_password and
search_password are different.
Perhaps you have two functions with similar names, and one is broken?
Colin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:36 ` Re: Function returns error (view) Colin Wetherbee <cww@denterprises.org>
@ 2008-02-26 16:48 ` Professor Flávio Brito <prof.flaviobrito@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Professor Flávio Brito @ 2008-02-26 16:48 UTC (permalink / raw)
To: Colin Wetherbee <cww@denterprises.org>; +Cc: pgsql-sql
<www.cecierj.rj.gov.br>
Hi Colin
When I translated from Portuguese to English I forgot a letter, but using
the corrected name I received an error.
2008/2/26, Colin Wetherbee <cww@denterprises.org>:
>
> Professor Flávio Brito wrote:
> > When I Test my view I receive
> >
> > SELECT seach_password('user_login_foo')
>
> [...]
>
> > ERROR: column "user_login_foo" does not exist
> > SQL state: 42703
> > Context: PL/pgSQL function "search_password" line 14 at for over execute
> > statement
>
>
> seach_password and
> search_password are different.
>
> Perhaps you have two functions with similar names, and one is broken?
>
>
> Colin
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
@ 2008-02-26 16:59 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Bart Degryse @ 2008-02-26 16:59 UTC (permalink / raw)
To: Professor Flávio Brito <prof.flaviobrito@gmail.com>; pgsql-sql
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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
@ 2008-02-26 18:20 ` Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-27 07:52 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 1 reply; 11+ messages in thread
From: Professor Flávio Brito @ 2008-02-26 18:20 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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;
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
@ 2008-02-27 07:52 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-27 09:09 ` Re: Function returns error (view) Markus Bertheau <mbertheau.pg@googlemail.com>
2008-02-27 20:42 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Bart Degryse @ 2008-02-27 07:52 UTC (permalink / raw)
To: Professor Flávio Brito <prof.flaviobrito@gmail.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-27 07:52 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
@ 2008-02-27 09:09 ` Markus Bertheau <mbertheau.pg@googlemail.com>
2008-02-27 09:16 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 1 reply; 11+ messages in thread
From: Markus Bertheau @ 2008-02-27 09:09 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: Professor Flávio Brito <prof.flaviobrito@gmail.com>; pgsql-sql
2008/2/27, Bart Degryse <Bart.Degryse@indicator.be>:
>
> I would also suggest you replace the
> ...t.cod_user IN (subselect)
> by a join construction. I think it's more performant.
In recent versions PostgreSQL is quite smart when planning IN, so that
shouldn't be a concern.
Markus
--
Markus Bertheau
Blog: http://www.bluetwanger.de/blog/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-27 07:52 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-27 09:09 ` Re: Function returns error (view) Markus Bertheau <mbertheau.pg@googlemail.com>
@ 2008-02-27 09:16 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-27 09:37 ` Re: Function returns error (view) Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Bart Degryse @ 2008-02-27 09:16 UTC (permalink / raw)
To: Markus Bertheau <mbertheau.pg@googlemail.com>; +Cc: pgsql-sql
For rather "simple" queries like this one PostgreSQL indeed seems to be quite smart.
I have quite a lot of statements where it does make a difference though (PostgreSQL 8.2.4).
As long as I have one statement where it makes a difference I will use the join
rather than the IN(subselect) just to be sure of optimal performance.
Keeping that in mind I don't see any harm in advising others to do so too.
But you're right in stating that it's not always necessary.
>>> "Markus Bertheau" <mbertheau.pg@googlemail.com> 2008-02-27 10:09 >>>
2008/2/27, Bart Degryse <Bart.Degryse@indicator.be>:
>
> I would also suggest you replace the
> ...t.cod_user IN (subselect)
> by a join construction. I think it's more performant.
In recent versions PostgreSQL is quite smart when planning IN, so that
shouldn't be a concern.
Markus
--
Markus Bertheau
Blog: http://www.bluetwanger.de/blog/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-27 07:52 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-27 09:09 ` Re: Function returns error (view) Markus Bertheau <mbertheau.pg@googlemail.com>
2008-02-27 09:16 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
@ 2008-02-27 09:37 ` Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Markus Bertheau @ 2008-02-27 09:37 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
2008/2/27, Bart Degryse <Bart.Degryse@indicator.be>:
>
>
> For rather "simple" queries like this one PostgreSQL indeed seems to be
> quite smart.
> I have quite a lot of statements where it does make a difference though
> (PostgreSQL 8.2.4).
I would rather find a situation where an explicit join is planned
significantly different than an equivalent IN to be a bug in
PostgreSQL:
Markus
--
Markus Bertheau
Blog: http://www.bluetwanger.de/blog/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-27 07:52 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
@ 2008-02-27 20:42 ` Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-28 07:45 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 1 reply; 11+ messages in thread
From: Professor Flávio Brito @ 2008-02-27 20:42 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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;
> >
> >
> >
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Function returns error (view)
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-27 07:52 ` Re: Function returns error (view) Bart Degryse <Bart.Degryse@indicator.be>
2008-02-27 20:42 ` Re: Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
@ 2008-02-28 07:45 ` Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 0 replies; 11+ messages in thread
From: Bart Degryse @ 2008-02-28 07:45 UTC (permalink / raw)
To: Professor Flávio Brito <prof.flaviobrito@gmail.com>; +Cc: pgsql-sql
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.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= 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;
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-02-28 07:45 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-26 16:32 Function returns error (view) Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:36 ` Colin Wetherbee <cww@denterprises.org>
2008-02-26 16:48 ` Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-26 16:59 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 18:20 ` Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-27 07:52 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-27 09:09 ` Markus Bertheau <mbertheau.pg@googlemail.com>
2008-02-27 09:16 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-27 09:37 ` Markus Bertheau <mbertheau.pg@googlemail.com>
2008-02-27 20:42 ` Professor Flávio Brito <prof.flaviobrito@gmail.com>
2008-02-28 07:45 ` Bart Degryse <Bart.Degryse@indicator.be>
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