From: | <jim(dot)kosloskey(at)jim-kosloskey(dot)com> |
---|---|
To: | <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Novice with Postgresql - trying simple Stored Procedure |
Date: | 2024-08-13 15:32:17 |
Message-ID: | 015301daed95$fb5654e0$f202fea0$@jim-kosloskey.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2024-08-13 16:05:47 | Re: Novice with Postgresql - trying simple Stored Procedure |
Previous Message | Greg Sabino Mullane | 2024-08-13 14:08:52 | Re: Column type modification in big tables |