From: Professor Flávio Brito <prof.flaviobrito@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Function returns error (view)
Date: Tue, 26 Feb 2008 13:32:18 -0300
Message-ID: <6a5e3a6f0802260832k9c1375ap632f8adad0740fa2@mail.gmail.com> (raw)
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: <6a5e3a6f0802260832k9c1375ap632f8adad0740fa2@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