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

Question

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)

This doesn't answer the question but I couldn't help but comment: why don't you simply retrieve all the data first from PostgreSQL then manipulate the data in Python? Surely Python is easier to work with than a PostgreSQL procedure? — wookie919
– wookie919, Commented Oct 22, 2014 at 1:18

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-10-22 02:09:59Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related