Novice with Postgresql - trying simple Stored Procedure - Mailing list pgsql-general

From
Subject Novice with Postgresql - trying simple Stored Procedure
Date
Msg-id 015301daed95$fb5654e0$f202fea0$@jim-kosloskey.com
Whole thread Raw
Responses Re: Novice with Postgresql - trying simple Stored Procedure
List pgsql-general

I have a simple 3 column table. The Table is a Month Name to number table which also includes an effective date column. So 12 rows, 3 columns each.

 

Here is the Stored Procedure I constructed to get the number if given the name (3 parameters for the SP):

 

--/
DROP PROCEDURE month_lookup
        (in mth_name TEXT,
        inout mth_num TEXT,
        inout ret_cd TEXT);
CREATE OR REPLACE PROCEDURE month_lookup
        (in mth_name TEXT,
        inout mth_num TEXT,
        inout ret_cd TEXT default '00000^00')
      LANGUAGE plpgsql AS $$
         BEGIN
           -- SET mth_num = num WHERE name = mth_name;
          SELECT
              DISTINCT month_nm_2_num.month_nm_2_num.num
          FROM
              month_nm_2_num.month_nm_2_num
          WHERE
             month_nm_2_num.month_nm_2_num.name = mth_name
          ORDER BY
             month_nm_2_num.month_nm_2_num.name ASC
           INTO mth_num;
          RAISE NOTICE '%', mth_num;
         RETURN;
         END; $$;
/

 

Here is the invocation of that SP:

 

--/
DO $$
DECLARE
        mth_name TEXT;
        ret_cd TEXT;
        mth_num TEXT;
BEGIN
        call month_lookup ('Jan','00',null);
        /* raise notice '%', mth_num;*/
END $$;
/

 

Here is the error I am receiving (using DB-Visualizer):

 

 

I have tried a lot of different things. I have researched a number of examples and I am stumped. I am fairly certain it is something simple and related to the parameter in the SP.

 

Can anybody straighten me out?

 

Thanks in advance,

 

Jim Kosloskey

Attachment

pgsql-general by date:

Previous
From: Greg Sabino Mullane
Date:
Subject: Re: Column type modification in big tables
Next
From: Ron Johnson
Date:
Subject: Re: Novice with Postgresql - trying simple Stored Procedure