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.
On 11/7/06, Alain Roger <raf.news@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxx> 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.
