RE: Novice with Postgresql - trying simple Stored Procedure

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 <ronljohnsonjr(at)gmail(dot)com>
Sent: Tuesday, August 13, 2024 12:06 PM
To: pgsql-generallists.postgresql.org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Novice with Postgresql - trying simple Stored Procedure

On Tue, Aug 13, 2024 at 11:32 AM <jim(dot)kosloskey(at)jim-kosloskey(dot)com <mailto:jim(dot)kosloskey(at)jim-kosloskey(dot)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 '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 <http://month_nm_2_num.month_nm_2_num.name> = mth_name
ORDER BY
month_nm_2_num.month_nm_2_num.name <http://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!