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> 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 = 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!