Thread: stored procedure / Function

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
$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 ?
thx.
AL.

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@magproductions.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 //