PostgreSQL Stored Procedures with text as parameters

Question

I have searched and could not find an open post regarding this topic:

I'm working with stored procedures in PostgreSQL and I'm currently being asked to create one that will collect certain information from a publisher based on the only parameter passed, their publishercode.

This code is in the existing table as a "character" type and as such I'm passing it the same and calling the function with that same type input, but I'm only obtaining a "NULL" value from my input and I'm not sure why.

This is my code:

CREATE OR REPLACE FUNCTION uspGetPubStatsMAC (pubcode publisher.publishercode%TYPE) -- Publisher Code 
RETURNS text AS
$$

DECLARE PubCode publisher.publishercode%TYPE;
    PubName text;
    NumOfAuth int;
    NumOfPubBooks int;
    HiValBook text;
    NumHiValBook int;
    TotNumHiValBook int;

BEGIN

-- Get the publisher's name from the given code

    RAISE   NOTICE 'Code: (%)', pubcode;

    SELECT  publishername INTO PubName
    FROM    publisher
    WHERE   publishercode = pubcode;

    RAISE   NOTICE 'Name: (%)', PubName;

    -- check if a publisher exists with the given code
    IF ( PubName is null )
    THEN
    RAISE   NOTICE 'No publisher exists with the given code (%)', pubcode;
    RETURN  (-1);
    END IF;

    RAISE NOTICE 'Current Statistics of the publisher (%)', pubcode;
    RAISE NOTICE '   The Name of the Publisher: (%)', trim (trailing ' ' from PubName);

    SELECT  count(distinct a.authornum) INTO NumOfAuth 
    FROM    publisher p, book b, wrote w, author a
    WHERE   p.publishercode = b.publishercode
      and   b.bookcode = w.bookcode;

    RAISE NOTICE '   The number of distinct authors who have written book(s) for this publisher: (%)', NumOfAuth;

END;
$$ language plpgsql;

SELECT uspGetPubStatsMAC('AH');

Where 'AH' is the code corresponding to publisher Arkham House in my table. Any idea of what I'm doing wrong?

Patrick · Accepted Answer · 2014-03-19 06:05:43Z

You have at least two errors in your code:

pubcode is the parameter passed in to the function. Then you declare a local PubCode variable. Remove that declaration.
Your function should RETURN some text value representing PUBstatsMAC when it succeeds. Currently you only return -1 if there is an error. At that point you should RAISE an error with an appropriate message and forget about a return value, otherwise a bad return value may get cascaded into other code.

Furthermore, after a SELECT ... INTO ... statement you should test for the FOUND internal variable rather than a NULL value in a local variable:

IF NOT FOUND THEN
  RAISE 'No publisher exists with the given code (%)', pubcode;
END IF;

And your query on number of distinct authors will give a bad result because you are missing a join clause on table author.

Then, you have a bunch of unused local variables declared.

Lastly, it is bad practice to return your information from a function in a bunch of RAISE NOTICE statements.

user3436079 · Accepted Answer · 2014-03-25 03:29:05Z

0

Thanks a lot for your feedback! I will test some of these fixes.

With respect to the return of -1 that is a requirement by the teacher. He wants a -1 if an error occurred at run time and a 0 if not. Hence why the output is formatted as raise notices. Also a requirement.

The extra local variables are there because that is an unfinished portion of the entire procedure code.

I tried what you suggested though and it worked like a charm. The extra declaration of PubCode was causing the problem.

Thanks so much!!!

answered Mar 25, 2014 at 3:29

user3436079

1

Collectives™ on Stack Overflow

PostgreSQL Stored Procedures with text as parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related