public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: '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:14:59 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaCEtXCpsvrqpeupWesM5KnuAxiiOhG_ruE-0_ABWJXY0w@mail.gmail.com>
References: <[email protected]>
	<CANzqJaCEtXCpsvrqpeupWesM5KnuAxiiOhG_ruE-0_ABWJXY0w@mail.gmail.com>

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 <[email protected]> 
Sent: Tuesday, August 13, 2024 12:06 PM
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Novice with Postgresql - trying simple Stored Procedure

 

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



Attachments:

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

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], [email protected]
  Subject: RE: Novice with Postgresql - trying simple Stored Procedure
  In-Reply-To: <[email protected]>

* 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