Thread: Function returns error (view)

Function returns error (view)

From

"Professor Flávio Brito"

Date:

26 February 2008, 15:32:27

Hi<br /><br />Don't know why I can't receive a return like my view fields (I'm newbie in plpgsql). Postgresql returns
mea erro . How can I received a answer like my view structure?<br /><br />When I Test my view I receive <br /><br
/>SELECT seach_password('user_login_foo')<br /><br /> My view returns me<br /><br /><font size="1"> 25746;"MARCELO
";"bio1";"bio1";"2008-02-19";"FALSE";"2008-02-12";"2008-02-1212:51:40.229282";"TRUE"<br /> 30356;"JOSE DE JESUS
";"977";"377";"2008-02-19";"FALSE";"2008-02-12";"2008-02-1212:52:19.688381";"TRUE"<br /></font><br /> It's OK<br /><br
/>but when I use function it returns me<br /><br /> ERROR: column "user_login_foo" does not exist<br /> SQL state:
42703<br/> Context: PL/pgSQL function "search_password" line 14 at for over execute statement<br /><br /> Where is my
fault?<br/><br />Thanks<br /><br />Flávio <br /><br
/>*************************************************************************************************************<br
/>vw_change_password attributes<br /><br />cod_user integer,<br /> user_name varchar(150),<br />openpsw varchar
(32),<br/>user_password varchar (50),<br />end timestamp,<br />validate boolean,<br />date_add timestamp,<br
/>user_timetimestamp,<br />ok boolean<br /><br /><br />CREATE OR REPLACE FUNCTION seach_password(USER_FOO
varchar(100))<br/> RETURNS SETOF vw_change_password AS <br />$BODY$<br />DECLARE <br />r vw_change_password%ROWTYPE;<br
/>USER_FOOalias for $1;<br />sql TEXT;<br />BEGIN<br />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<br />    FROM usuario u, change_user_password t<br /> 
WHEREu.cod_user = t.cod_user  AND t.cod_user <br />    IN <br />    (SELECT cod_user <br />        FROM table_user <br
/>           WHERE login='||USER_FOO||')';<br /><br /> FOR r IN EXECUTE sql<br /> LOOP<br /> RETURN NEXT r;<br />END
LOOP;<br/> IF NOT FOUND THEN<br />        RAISE EXCEPTION 'USER not found', USER_FOO;<br /> END IF;<br /> RETURN;<br
/> END<br/> $BODY$<br />  LANGUAGE 'plpgsql' VOLATILE;<br /><br /><br />

Re: Function returns error (view)

From

Colin Wetherbee

Date:

26 February 2008, 15:36:57

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

Re: Function returns error (view)

From

"Professor Flávio Brito"

Date:

26 February 2008, 15:49:09

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

Re: Function returns error (view)

From

"Bart Degryse"

Date:

26 February 2008, 16:00:23

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;

Re: Function returns error (view)

From

"Professor Flávio Brito"

Date:

26 February 2008, 17:23:15

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;

Re: Function returns error (view)

From

"Bart Degryse"

Date:

27 February 2008, 06:53:18

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;

Re: Function returns error (view)

From

"Markus Bertheau"

Date:

27 February 2008, 08:10:19

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/

Re: Function returns error (view)

From

"Bart Degryse"

Date:

27 February 2008, 08:17:10

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/

Re: Function returns error (view)

From

"Markus Bertheau"

Date:

27 February 2008, 08:44:04

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/

Re: Function returns error (view)

From

"Professor Flávio Brito"

Date:

27 February 2008, 19:42:28

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;

Re: Function returns error (view)

From

"Bart Degryse"

Date:

28 February 2008, 06:46:10

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');

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;