Re: stored procedure / Function

Alain Roger wrote:
> Hi,
>
> How to retrieve the IN parameter of a function for later use ?
>
> i tried this :
>
> CREATE OR REPLACE FUNCTION "public"."SP_U_001" ("TypeOfArticle" varchar)
> RETURNS SETOF "public"."active_articles" AS

My advice: Don't quote your identifiers unless you really really want
them to be case sensitive. Usually there's no benefit, and it can add
quite a bit of confusion ("What do you mean my function does not
exist?!? It's right here!").

> $body$
> DECLARE
> TypeArt VARCHAR := TypeOfArticle;
> rec RECORD;
> res active_articles;
> /**************************************/
> BEGIN
>
> SELECT articletypes.articletype_id INTO tpart FROM articletypes
> WHERE articletypes.articletype_type = TypeArt;
> ....
>
> but it seems that TypeArt VARCHAR := TypeOfArticle; does not
> work....
>
> Can you help me ?

I don't think that assignments in the declare block are allowed. You
could make your variable an alias though:
DECLARE
TypeArt ALIAS FOR "TypeOfArticle";

You could also move the assignment into the body of the function.

Although I wonder why you don't just use the IN parameter.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //