Обсуждение: data does not exist :-(
Hi,
Finally, i've come to the following solution for my stored procedure :
-----------------------------------
-- Function: "SP_U_001"("TypeOfArticle" "varchar")
-- DROP FUNCTION "SP_U_001"("TypeOfArticle" "varchar");
CREATE OR REPLACE FUNCTION "SP_U_001"("TypeOfArticle" "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;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "SP_U_001"("TypeOfArticle" "varchar") OWNER TO immensesk;
GRANT EXECUTE ON FUNCTION "SP_U_001"("TypeOfArticle" "varchar") TO immensesk;
-----------------------------------
however, when i run it thanks --> select SP_U_001('action');
i get the following answer : ERROR: function sp_u_001("unknown") does not exist
in pgAdmin III GUI application
But this function exists.
So where is my mistake ?
thx,
Al.
Finally, i've come to the following solution for my stored procedure :
-----------------------------------
-- Function: "SP_U_001"("TypeOfArticle" "varchar")
-- DROP FUNCTION "SP_U_001"("TypeOfArticle" "varchar");
CREATE OR REPLACE FUNCTION "SP_U_001"("TypeOfArticle" "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;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "SP_U_001"("TypeOfArticle" "varchar") OWNER TO immensesk;
GRANT EXECUTE ON FUNCTION "SP_U_001"("TypeOfArticle" "varchar") TO immensesk;
-----------------------------------
however, when i run it thanks --> select SP_U_001('action');
i get the following answer : ERROR: function sp_u_001("unknown") does not exist
in pgAdmin III GUI application
But this function exists.
So where is my mistake ?
thx,
Al.
On 11/6/06, Alain Roger <raf.news@gmail.com> wrote: > Hi, > > Finally, i've come to the following solution for my stored procedure : > ----------------------------------- > -- Function: "SP_U_001"("TypeOfArticle" "varchar") > > -- DROP FUNCTION "SP_U_001"("TypeOfArticle" "varchar"); > > CREATE OR REPLACE FUNCTION "SP_U_001"("TypeOfArticle" "varchar") > however, when i run it thanks --> select SP_U_001('action'); if you double quote a function (or anything else)'s name when you create it, you must also double quote it when you call it: select "SP_U_001"('action'); I would advise you to use lower case names for things to avoid having to do this. merlin