Обсуждение: Function returns error (view)

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

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;

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;

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;

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;