Novice with Postgresql - trying simple Stored Procedure

[jim.kosloskey]

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 '0^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

RE: Novice with Postgresql - trying simple Stored Procedure

[jim.kosloskey]

Ron,

 

Thank you – that got me past that. I thought I had used a variable before with 
no joy but I probably had other issues.

 

Hopefully, I am on my way now.

 

Thanks again,

 

Jim

 

From: Ron Johnson  
Sent: Tuesday, August 13, 2024 12:06 PM
To: pgsql-generallists.postgresql.org 
Subject: Re: Novice with Postgresql - trying simple Stored Procedure

 

On Tue, Aug 13, 2024 at 11:32 AM mailto:jim.koslos...@jim-kosloskey.com> > wrote:

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 '0^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.

 

PG wants to be able to write to mnth_num, since it's an inout param.  But it 
can't write to the constant literal '00'.

 

Thus, I'd try calling month_lookup with a variable that's set to '00'.

 

-- 

Death to America, and butter sauce.

Iraq lobster!