public inbox for [email protected]  

help / color / mirror / Atom feed

Novice with Postgresql - trying simple Stored Procedure
2+ messages / 2 participants
[nested] [flat]

* Novice with Postgresql - trying simple Stored Procedure
@ 2024-08-13 15:32  [email protected]
  0 siblings, 1 reply; 2+ messages in thread

From: [email protected] @ 2024-08-13 15:32 UTC (permalink / raw)
  To: [email protected]

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



Attachments:

  [image/png] image001.png (18.3K, 3-image001.png)
  download | view image

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Novice with Postgresql - trying simple Stored Procedure
@ 2024-08-13 16:05  Ron Johnson <[email protected]>
  parent: [email protected]
  0 siblings, 0 replies; 2+ messages in thread

From: Ron Johnson @ 2024-08-13 16:05 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <[email protected]>

On Tue, Aug 13, 2024 at 11:32 AM <[email protected]> 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!


Attachments:

  [image/png] image001.png (18.3K, 3-image001.png)
  download | view image

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-08-13 16:05 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-13 15:32 Novice with Postgresql - trying simple Stored Procedure [email protected]
2024-08-13 16:05 ` Ron Johnson <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox