0

So, Im trying to work on a simple book loans system and Im having problems on creating and using a function.

I have a Loans 'Table', Copies 'Table' and Available 'View'.

"Available View" looks like this:

 book_id | available_copies
---------+------------------
 BI6     |                1

wherein 'available_copies' column is 

COUNT(copy_id) AS available_copies 
FROM copies 
WHERE copy_id NOT IN (SELECT copy_id FROM loans)

This is my "Copies Table"

 copy_id | book_id | copy_no | copy_code
---------+---------+---------+-----------
 CI8     | BI6     |       8 | CI
 CI9     | BI6     |       9 | CI
 CI7     | BI7     |       7 | CI
 CI10    | BI7     |      10 | CI

and this is my "Loans Table"

 loan_id | copy_id | user_id | borrow_date |  due_date  | loan_no | loan_code
---------+---------+---------+-------------+------------+---------+-----------
 LI10    | CI10    | UI4     | 2013-05-21  | 2013-05-26 |      10 | LI
 LI11    | CI8     | UI4     | 2013-05-21  | 2013-05-26 |      11 | LI
 LI12    | CI7     | UI4     | 2013-05-22  | 2013-05-27 |      12 | LI

What i really wanted to do is.. if the available_copies is 0 (like in the "available view" above, BI7 is not in the Available View anymore because all copies where already borrowed) postgres will prompt something that you cannot borrow books in Loans anymore since the book is already out of copies.

Im kinda new to plpgsql. Please help. :(

Improve this question
3
  • ALWAYS show your exact PostgreSQL version. See select version(). Commented May 21, 2013 at 23:52
  • PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit Commented May 22, 2013 at 2:39
  • There's no way the code in the unedited question could possibly have run, then, it fails to even compile. Commented May 22, 2013 at 2:49

2 Answers 2

Reset to default
1

I don't know what Pg version you has, but probably some older. I see lot of bugs in your example - so I don't believe it was accepted by postgres

CREATE OR REPLACE FUNCTION try(copyID TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    SELECT available_copies FROM available -- missing INTO ??? 
    -- Undeclared variable "available_copies" and probably
    -- collision with column named "available_copies" 
    IF available_copies > 0 THEN 
       INSERT INTO loans(copy_id) VALUES(copyID);
       RETURN BOOLEAN; --- RETURN true or false, but BOOLEAN??
    ELSE
       RETURN BOOLEAN;
    END IF;
END;
$$ LANGUAGE plpgsql;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Believe me, It was accepted by postgres and I cant believe it either thats why I asked. HAHAHA. Im using psql 9.2. I dont have any background on plpgsql I kinda rushed things out. Are there any ways you can think of on how solve my problem??
@user2406280: Your information is impossible. The script in your question cannot be executed as it is. There must be some kind of misunderstanding.
Oh, Is that so?? Well, if its impossible, then, are there any ways on how i can check the availability of the book through plpgsql functions?? Sorry for bothering, I'm really new to plpgsql.
@user2406280 The are plenty of ways to check availability of the book through plpgsql functions. He just says, that the function code you provided is invalid.
Yes, I know. But can you give me an example??
|
0

Example of PL/SQL function:

CREATE OR REPLACE FUNCTION try(copyID TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN NOT EXISTS (SELECT l.copy_id FROM loans l WHERE l.copy_id = copyID);
END;
$$ LANGUAGE plpgsql;

It will RETURN TRUE if the record with copyID NOT EXISTS in loans;

Same function as SQL function:

CREATE OR REPLACE FUNCTION try(copyID TEXT)
RETURNS BOOLEAN AS $$
    SELECT NOT EXISTS (SELECT l.copy_id FROM loans l WHERE l.copy_id = copyID)
$$ LANGUAGE SQL;
Improve this answer

8 Comments

if i enter a copy_id that is already in the loans table, will this function immediately work?? It doesn't need anything to make it work??
It only rely on table loans with field copy_id.
I tried it but it doesnt work?? Maybe because all "copy_id"s were results of concatenated columns, copy_code(char) and copy_no(serial)??
Without any error messages or explanations how it doesnt work I can only guess.
Search engine you are using does a correct thing. Postgers official manual is all you need now. The other books, manuals and mailing lists are for things you dont need atm.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.