public.login
Documentation | Dell DVD Store |
Schema | public |
Name | login |
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | OUT | Returns | refcursor | |
2 | IN | username_in | text | Customers' username |
Customers' username | ||||
3 | IN | password_in | text | Customers' pasword |
Customers' pasword |
Script
CREATE OR REPLACE FUNCTION public.login(username_in text, password_in text) RETURNS SETOF refcursor LANGUAGE plpgsql AS $function$ DECLARE customerid_out INT; ref1 refcursor; ref2 refcursor; BEGIN SELECT CUSTOMERID INTO customerid_out FROM CUSTOMERS WHERE USERNAME=username_in AND PASSWORD=password_in; IF FOUND THEN OPEN ref1 FOR SELECT customerid_out; RETURN NEXT ref1; OPEN ref2 FOR SELECT derivedtable1.TITLE, derivedtable1.ACTOR, PRODUCTS_1.TITLE AS RelatedPurchase FROM (SELECT PRODUCTS.TITLE, PRODUCTS.ACTOR, PRODUCTS.PROD_ID, PRODUCTS.COMMON_PROD_ID FROM CUST_HIST INNER JOIN PRODUCTS ON CUST_HIST.PROD_ID = PRODUCTS.PROD_ID WHERE (CUST_HIST.CUSTOMERID = customerid_out)) AS derivedtable1 INNER JOIN PRODUCTS AS PRODUCTS_1 ON derivedtable1.COMMON_PROD_ID = PRODUCTS_1.PROD_ID; RETURN NEXT ref2; ELSE OPEN ref1 FOR SELECT 0; RETURN NEXT ref1; END IF; RETURN ; END; $function$ |
Exported: 2018-11-09 16:14, Last imported: 2018-08-06 09:39