I'm lost :-( with FOR...IN
Hi,
I' still with my stored procedure :
-- Function: SP_U_001(typeofarticle varchar)
-- DROP FUNCTION SP_U_001(typeofarticle varchar);
CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)
RETURNS SETOF active_articles AS
$BODY$
DECLARE
myrec RECORD;
res active_articles;
/**************************************/
BEGIN
FOR myrec IN
select *
from articles, articletypes, department
where
articletypes.articletype_type = $1
AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id
AND articles.validity_period_end > now()
LOOP
IF (myrec IS NOT NULL) THEN
res.article_type := myrec.articletypes.articletype_type;
res.article_author := myrec.articles.author;
res.department_owner := myrec.department.department_name;
res.department_picture := myrec.department.department_picture;
res.article_title := myrec.articles.title;
res.article_content := myrec.articles.content;
res.date_creation := myrec.articles.creation_date;
res.date_start := myrec.articles.validity_period_start;
res.date_end := myrec.articles.validity_period_end;
END IF;
RETURN NEXT res;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk;
GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;
whatever, i do the argument VARCHAR will be stored in double quote as
"varchar" when i check via pgAdmin GUI. Even if in command line it looks
like above.
I still have the same error message on "myrec" :
ERROR: schema "myrec" does not exist
CONTEXT: SQL statement "SELECT myrec.articletypes.articletype_type"
i do not understand as there is quite the same example in postgreSQl
8.1.4documentation on page 623-624 about "Looping Through Query
Results".
so where am i wrong ?
Al.
On 11/7/06, Alain Roger <raf.news@gmail.com> wrote:
Hi,
I' still with my stored procedure :
-- Function: SP_U_001(typeofarticle varchar)
-- DROP FUNCTION SP_U_001(typeofarticle varchar);
CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)
RETURNS SETOF active_articles AS
$BODY$
DECLARE
myrec RECORD;
res active_articles;
/**************************************/
BEGIN
FOR myrec IN
select *
from articles, articletypes, department
where
articletypes.articletype_type = $1
AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id
AND articles.validity_period_end > now()
LOOP
IF (myrec IS NOT NULL) THEN
res.article_type :=
myrec.articletypes.articletype_type;
res.article_author := myrec.articles.author;
res.department_owner :=
myrec.department.department_name ;
res.department_picture :=
myrec.department.department_picture;
res.article_title := myrec.articles.title;
res.article_content := myrec.articles.content;
res.date_creation := myrec.articles.creation_date ;
res.date_start :=
myrec.articles.validity_period_start;
res.date_end := myrec.articles.validity_period_end;
END IF;
RETURN NEXT res;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk;
GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;whatever, i do the argument VARCHAR will be stored in double quote as
"varchar" when i check via pgAdmin GUI. Even if in command line it looks
like above.I still have the same error message on "myrec" :
ERROR: schema "myrec" does not exist
CONTEXT: SQL statement "SELECT
myrec.articletypes.articletype_type"
you are using composite types right? you have to add parenthesis to
disambiguate this case:
http://www.postgresql.org/docs/8.1/interactive/rowtypes.html#AEN5789
res.article_title := myrec.(articles).title;
sorry i missed that the first time out.
merlin
If i do what you wrote, i can not create the function into my DB.
error on 1st (
Show quoted text
On 11/7/06, Merlin Moncure <mmoncure@gmail.com> wrote:
On 11/7/06, Alain Roger <raf.news@gmail.com> wrote:
Hi,
I' still with my stored procedure :
-- Function: SP_U_001(typeofarticle varchar)
-- DROP FUNCTION SP_U_001(typeofarticle varchar);
CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)
RETURNS SETOF active_articles AS
$BODY$
DECLARE
myrec RECORD;
res active_articles;
/**************************************/
BEGIN
FOR myrec IN
select *
from articles, articletypes, department
where
articletypes.articletype_type = $1
AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id
AND articles.validity_period_end > now()
LOOP
IF (myrec IS NOT NULL) THEN
res.article_type :=
myrec.articletypes.articletype_type;
res.article_author := myrec.articles.author;
res.department_owner :=
myrec.department.department_name ;
res.department_picture :=
myrec.department.department_picture;
res.article_title := myrec.articles.title;
res.article_content := myrec.articles.content;
res.date_creation := myrec.articles.creation_date ;
res.date_start :=
myrec.articles.validity_period_start;
res.date_end := myrec.articles.validity_period_end;
END IF;
RETURN NEXT res;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk;
GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;whatever, i do the argument VARCHAR will be stored in double quote as
"varchar" when i check via pgAdmin GUI. Even if in command line it looks
like above.I still have the same error message on "myrec" :
ERROR: schema "myrec" does not exist
CONTEXT: SQL statement "SELECT
myrec.articletypes.articletype_type"you are using composite types right? you have to add parenthesis to
disambiguate this case:http://www.postgresql.org/docs/8.1/interactive/rowtypes.html#AEN5789
res.article_title := myrec.(articles).title;
sorry i missed that the first time out.
merlin
On Tue, 7 Nov 2006, Alain Roger wrote:
Hi,
I' still with my stored procedure :
-- Function: SP_U_001(typeofarticle varchar)
-- DROP FUNCTION SP_U_001(typeofarticle varchar);
CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)
RETURNS SETOF active_articles AS
$BODY$
DECLARE
myrec RECORD;
res active_articles;
/**************************************/
BEGIN
FOR myrec IN
select *
from articles, articletypes, department
where
articletypes.articletype_type = $1
AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id
AND articles.validity_period_end > now()
LOOP
IF (myrec IS NOT NULL) THEN
res.article_type := myrec.articletypes.articletype_type;
I don't think the column names are going to keep their originating table
name inside the record, so the field probably needs
to be referred to as myrec.articletype_type not
myrec.articletypes.articletype_type.
i already tried this possibility and i've got :
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "sp_u_001" line 26 at return next
:-(
Show quoted text
On 11/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
On Tue, 7 Nov 2006, Alain Roger wrote:
Hi,
I' still with my stored procedure :
-- Function: SP_U_001(typeofarticle varchar)
-- DROP FUNCTION SP_U_001(typeofarticle varchar);
CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)
RETURNS SETOF active_articles AS
$BODY$
DECLARE
myrec RECORD;
res active_articles;
/**************************************/
BEGIN
FOR myrec IN
select *
from articles, articletypes, department
where
articletypes.articletype_type = $1
AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id
AND articles.validity_period_end > now()
LOOP
IF (myrec IS NOT NULL) THEN
res.article_type := myrec.articletypes.articletype_type;I don't think the column names are going to keep their originating table
name inside the record, so the field probably needs
to be referred to as myrec.articletype_type not
myrec.articletypes.articletype_type.
Ok guys...i found the stupid problem :-((
everytime that i call my stored procedure, i did like that : select
sp_u_001('action'); instead of select * from sp_u_001('action');
thanks to all of you for your tips, they helped me to understand composite.
one last question : how can i test if myrec composite has some records ?
i was thinking about == > select count(*) from (myrec); but i'm not sure
about the logic of this command.
Al.
Show quoted text
On 11/7/06, Alain Roger <raf.news@gmail.com> wrote:
i already tried this possibility and i've got :
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "sp_u_001" line 26 at return next:-(
On 11/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
On Tue, 7 Nov 2006, Alain Roger wrote:
Hi,
I' still with my stored procedure :
-- Function: SP_U_001(typeofarticle varchar)
-- DROP FUNCTION SP_U_001(typeofarticle varchar);
CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR)
RETURNS SETOF active_articles AS
$BODY$
DECLARE
myrec RECORD;
res active_articles;
/**************************************/
BEGIN
FOR myrec IN
select *
from articles, articletypes, department
where
articletypes.articletype_type = $1
AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id
AND articles.validity_period_end > now()
LOOP
IF (myrec IS NOT NULL) THEN
res.article_type := myrec.articletypes.articletype_type;I don't think the column names are going to keep their originating table
name inside the record, so the field probably needs
to be referred to as myrec.articletype_type not
myrec.articletypes.articletype_type.