public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Novice with Postgresql - trying simple Stored Procedure
Date: Tue, 13 Aug 2024 12:05:47 -0400
Message-ID: <CANzqJaCEtXCpsvrqpeupWesM5KnuAxiiOhG_ruE-0_ABWJXY0w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Novice with Postgresql - trying simple Stored Procedure
  In-Reply-To: <CANzqJaCEtXCpsvrqpeupWesM5KnuAxiiOhG_ruE-0_ABWJXY0w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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