public.new_customer
Documentation | Dell DVD Store |
Schema | public |
Name | new_customer |
Function to add a new customer.
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | OUT | Returns | integer | Returns customerid. |
Returns customerid. | ||||
2 | IN | firstname_in | character varying | The customer's first name. |
The customer's first name. | ||||
3 | IN | lastname_in | character varying | The customer's last name. |
The customer's last name. | ||||
4 | IN | address1_in | character varying | The first line of an address. |
The first line of an address. | ||||
5 | IN | address2_in | character varying | An optional second line of an address. |
An optional second line of an address. | ||||
6 | IN | city_in | character varying | The name of the city. |
The name of the city. | ||||
7 | IN | state_in | character varying | Standard state or province code. |
Standard state or province code. | ||||
8 | IN | zip_in | character varying | The postal code or ZIP code of the address (where applicable). |
The postal code or ZIP code of the address (where applicable). | ||||
9 | IN | country_in | character varying | The country name. |
The country name. | ||||
10 | IN | region_in | smallint | Region id. |
Region id. | ||||
11 | IN | email_in | character varying | The customer's email address. |
The customer's email address. | ||||
12 | IN | phone_in | character varying | The telephone number for the address. |
The telephone number for the address. | ||||
13 | IN | creditcardtype_in | integer | Credit card expiration date type. |
Credit card expiration date type. | ||||
14 | IN | creditcard_in | character varying | Credit card identification number. |
Credit card identification number. | ||||
15 | IN | creditcardexpiration_in | character varying | Credit card expiration date. |
Credit card expiration date. | ||||
16 | IN | username_in | character varying | Account username. |
Account username. | ||||
17 | IN | password_in | character varying | Account password. |
Account password. | ||||
18 | IN | age_in | smallint | The customer's age. |
The customer's age. | ||||
19 | IN | income_in | integer | The customer's income. |
The customer's income. | ||||
20 | IN | gender_in | character varying | M = Male, F = Female |
M = Male, F = Female |
Script
CREATE OR REPLACE FUNCTION public.new_customer(firstname_in character varying, lastname_in character varying, address1_in character varying, address2_in character varying, city_in character varying, state_in character varying, zip_in character varying, country_in character varying, region_in smallint, email_in character varying, phone_in character varying, creditcardtype_in integer, creditcard_in character varying, creditcardexpiration_in character varying, username_in character varying, password_in character varying, age_in smallint, income_in integer, gender_in character varying) RETURNS integer LANGUAGE plpgsql AS $function$ DECLARE customerid_out INTEGER; age_int INTEGER; income_int INTEGER; BEGIN -- IF age_in = '' THEN age_int:=0 ; ELSE age_int := CAST (age_in AS INT); END IF; -- IF income_in = '' THEN income_int:=0; ELSE income_int := CAST (income_in AS INT); END IF; BEGIN INSERT INTO CUSTOMERS ( firstname, lastname, email, phone, username, password, address1, address2, city, state, zip, country, region, creditcardtype, creditcard, creditcardexpiration, age, income, gender ) VALUES ( firstname_in, lastname_in, email_in, phone_in, username_in, password_in, address1_in, address2_in, city_in, state_in, zip_in, country_in, region_in, creditcardtype_in, creditcard_in, creditcardexpiration_in, age_int, income_int , gender_in ) RETURNING customerid INTO customerid_out; RETURN customerid_out; EXCEPTION WHEN unique_violation THEN RETURN 0; END; -- RETURN -1; END; $function$ |
Exported: 2018-11-09 16:14, Last imported: 2018-08-06 09:39