Skip site navigation (1) Skip section navigation (2)

Re: Function returns error (view)

From: Professor Flávio Brito <prof(dot)flaviobrito(at)gmail(dot)com>
To:
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Function returns error (view)
Date: 2008-02-26 18:20:39
Message-ID: 6a5e3a6f0802261020o51327bceof4845c47ae1b099d@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(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;
>
>
>

In response to

Responses

pgsql-sql by date

Next:From: Edward W. RouseDate: 2008-02-26 19:29:45
Subject: Re: duplicate key violates unique constraint
Previous:From: Colin WetherbeeDate: 2008-02-26 17:33:46
Subject: Re: [SQL] Deploying PostgreSQL on virtualized hardware

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group