CODEWITHSUNDEEP
pythonpostgresqlpgadmin
teutonic_surf_777
teutonic_surf_777

Reputation: 11

How do you create a postgresql function that can be called using python script?

I have an assignment that requires me to extract several rows from a table in pgAdminIII, manipulate the row data into several new rowtypes (or just generate the data somehow).

Because the actual manipulation seemed to difficult to do using basic sql commands, I decided to try to make a postgresql stored procedure (or function) instead. So far all I'm getting are syntax errors.

My postgresql function: 

CREATE FUNCTION getAllPayments(double precision, double precision, timestamp) RETURNS text AS $$
DECLARE
credLim ALIAS FOR $1;
paid ALIAS FOR $2;
pdate ALIAS FOR $3;
totalpayments double precision;
numberofpayments integer;
availablecredit double precision;
BEGIN
IF payments.customernumber = orders.customernumber THEN
	numberofpayments := COUNT(pdate);
	totalpayments := SUM(paid);
	availablecredit := credLim + totalpayments - numberofpayments;
SELECT customers.customername, customers.customernumber, credLim, availablecredit, totalpayments, numberofpayments from customers, payments;
ELSE
Return "failed attempt";
END IF;

END;

And the python script that is calling it: 

get_data = "getAllPayments(customers.creditlimit, payments.amount, payments.paymentdate)"
seperator = "---------------------------------------------------------------------------------"

crsr2 = conn.cursor()
crsr2.execute(get_data)

print('Customer Number   Customer Name   Payments Made   Value of Orders   Credit Limit   Available Credit')
print(seperator)
for x in crsr2:
    neu = str(x)
    w = neu.replace(', ', ',     ')
    print(w)
print(seperator)

Upvotes: 1

Views: 363

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95761

I see

ERROR: unterminated dollar-quoted string at or near "$$

Terminate that string, and tell the dbms which language you're using.

CREATE FUNCTION getAllPayments(double precision, double precision, timestamp) 
RETURNS text AS $$
DECLARE
    credLim ALIAS FOR $1;
    paid ALIAS FOR $2;
    pdate ALIAS FOR $3;
    totalpayments double precision;
    numberofpayments integer;
    availablecredit double precision;
BEGIN
    IF payments.customernumber = orders.customernumber THEN
        numberofpayments := COUNT(pdate);
        totalpayments := SUM(paid);
        availablecredit := credLim + totalpayments - numberofpayments;

        SELECT customers.customername, customers.customernumber, credLim, availablecredit, totalpayments, numberofpayments from customers, payments;

    ELSE
        Return "failed attempt";
    END IF;
END;
$$                   -- Terminate the string.
language plpgsql;    -- Identify the language.

That should let it compile. I didn't try to see whether it made sense, but I noticed that the line

        availablecredit := credLim + totalpayments - numberofpayments;

looks a little suspicious, and not using ANSI joins is usually a bad idea.

Upvotes: 2

Related Questions