0

I have created a stored procedure in Postgres. It's about to get max of message_id from table Messages and store in another table (MaxMessageID) in column MessageID.

Here is my stored procedure:

CREATE OR REPLACE FUNCTION MaxId()
RETURNS integer AS $MID$
declare
    MID integer;
BEGIN
    DELETE FROM MaxMessageID;
    INSERT INTO MaxMessageID(MessageID)
        SELECT MAX(MESSAGE_ID) FROM Messages;
   RETURN MID;
END;
$MID$ LANGUAGE plpgsql;

After running this procedure, max(MESSAGE_ID) should be stored in MaxMessageID(MessageID).

When I run the procedure and check following command:

  Select * from MaxMessageID

It shows this. But when I call procedure with Select MaxId(), it shows this.

What am I missing?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Kate
  • 275
  • 1
  • 4
  • 15
  • What is in `Messages`? Can you show the output of `SELECT MAX(MESSAGE_ID) FROM Messages;`? – Michael Ambrose May 12 '16 at 15:32
  • @MichaelAmbrose, see here's the output http://prnt.sc/b37smr – Kate May 12 '16 at 15:37
  • Next I'd try and see if the issue is the function itself or the query. What happens when you run `INSERT INTO MaxMessageID(MessageID) SELECT MAX(MESSAGE_ID) FROM Messages;` ? Subsequent runs should keep adding rows to MaxMessageID. – Michael Ambrose May 12 '16 at 15:41
  • Please include clarifications to the question by clicking [edit] and adding text to the question itself. You'll get the best answers when the question can be answered without reading any comments or clicking any links, and when people can copy-and-paste key parts to test with or use in examples. – IMSoP May 12 '16 at 15:46
  • @MichaelAmbrose, it shows the right result, getting MAX(MESSAGE_ID) FROM Messages and storing into MaxMessageID(MessageID).... – Kate May 12 '16 at 15:46
  • 1
    First: where is the value assigned to `MID` variable in the your function? – Abelisto May 12 '16 at 15:48
  • Kate- is the issue that you're not getting results from `SELECT * FROM MaxMessageID` after you run `MaxId()` or that you're not getting a result from `MaxId()`? – Michael Ambrose May 12 '16 at 15:51
  • @MichaelAmbrose, both are not happening, I've edited my question, you can see screenshot. Tried both..no where getting result neither running SELECT * FROM MaxMessageID after running MaxId() either Select MaxId(). – Kate May 12 '16 at 16:06
  • 1
    The second one makes sense. You **should** be getting null since you haven't assigned a value to `MID`. Can you add `SELECT INTO MID MAX(MESSAGE_ID) FROM Messages; raise notice '%', MID;` into your function and run it again? – Michael Ambrose May 12 '16 at 16:11
  • [So do you have your answer?](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Erwin Brandstetter May 28 '16 at 00:53

1 Answers1

0

I have created a one stored procedure in Postgres.

Postgres does not have "stored procedures". Only functions, doing almost but not quite the same:

... when I check my output table

How do you check exactly? Your function is going to return NULL in any case:

RETURN MID;

And MID has never been assigned. Your return value does not make sense so far.

Check by looking into the target table MaxMessageID with SELECT * FROM MaxMessageID. If you see a single row with maxmessageid IS NULL, there are two possible explanations:

  1. No values in column message_id in table messages. Then the result is by design.

  2. You are confusing table or column names. Your unquoted CaMeL-case identifiers are suspicious that way. Are you aware that Postgres identifiers are case sensitive?

    And there can be multiple schemas in a Postgres database:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I understand your concepts, but my only concern, why am not getting the value in column message_id of table MaxMessageID. I'm calling procedure like this Select MaxId(); My individual query is working fine...Am I missing anything to get the desired result ? – Kate May 12 '16 at 16:03
  • @Kate: There is no column `message_id`. There seems to be a `"MaxMessageID"."MessageID"` or a `maxmessageid.messageid`, depending on whether you have used double-quotes when creating it or not. My standing advice is to use legal, lower-case, unquoted identifiers exclusively to avoid any such confusion. Follow the links I provided and read the manual about identifiers. – Erwin Brandstetter May 12 '16 at 16:06
  • I'd be careful calling Postgres identifiers case-sensitive. They're case-sensitive if they're in double quotes. If she were using an invalid identifier she'd be getting errors, not null responses. – Michael Ambrose May 12 '16 at 16:09
  • @MichaelAmbrose: Or there are *two* (or more) tables. – Erwin Brandstetter May 12 '16 at 16:11
  • Fair enough. I probably shouldn't assume this is a completely clean environment. – Michael Ambrose May 12 '16 at 16:12