From: | "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be> |
---|---|
To: | Professor Flávio Brito <prof(dot)flaviobrito(at)gmail(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Function returns error (view) |
Date: | 2008-02-28 07:45:51 |
Message-ID: | 47C674BE.A3DD.0030.0@indicator.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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;
From | Date | Subject | |
---|---|---|---|
Next Message | Professor Flávio Brito | 2008-02-28 16:01:41 | Re: Function returns error (view) (RESOLVED) |
Previous Message | Tom Lane | 2008-02-28 05:30:44 | Re: operator class |