CREATE OR REPLACE
FUNCTION get_member_id
( IN pv_account_number VARCHAR ) RETURNS INTEGER AS
$$
/* Required for PL/pgSQL programs. */
DECLARE
/* Local return variable. */
lv_retval INTEGER := 0; -- Default value is 0.
/* Use a cursor, which will not raise an exception at runtime. */
find_member_id CURSOR
( cv_account_number VARCHAR ) FOR
SELECT m.member_id
FROM member m
WHERE m.account_number = cv_account_number;
BEGIN
/* Assign a value when a row exists. */
FOR i IN find_member_id(pv_account_number) LOOP
lv_retval := i.member_id;
END LOOP;
/* Return 0 when no row found and the ID # when row found. */
RETURN lv_retval;
END;
$$ LANGUAGE plpgsql;
SELECT retval AS "Return Value"
FROM (SELECT get_member_id(m.account_number) AS retval
FROM member m
ORDER BY m.account_number LIMIT 1) x
ORDER BY 1;
CREATE OR REPLACE
FUNCTION get_system_user_id
( IN pv_user_name VARCHAR ) RETURNS INTEGER AS
$$
/* Required for PL/pgSQL programs. */
DECLARE
/* Local return variable. */
lv_retval INTEGER := 0; -- Default value is 0.
/* Use a cursor, which will not raise an exception at runtime. */
find_system_user_id CURSOR
( cv_user_name VARCHAR ) FOR
SELECT system_user_id
INTO lv_system_user_id
FROM system_user
WHERE system_user_name = pv_user_name;
BEGIN
/* Assign a value when a row exists. */
FOR i IN find_system_user_id(pv_user_name) LOOP
lv_retval := i.system_user_id;
END LOOP;
/* Return 0 when no row found and the ID # when row found. */
RETURN lv_retval;
END;
$$ LANGUAGE plpgsql;
SELECT retval AS "Return System Value"
FROM (SELECT get_system_user_id(su.system_user_name) AS retval
FROM system_user su
WHERE system_user_name LIKE 'DBA%' LIMIT 5) x
ORDER BY 1;
CREATE OR REPLACE
FUNCTION get_lookup_id
( IN pv_table_name VARCHAR
, IN pv_column_name VARCHAR
, IN pv_lookup_type VARCHAR ) RETURNS INTEGER AS
$$
/* Required for PL/pgSQL programs. */
DECLARE
/* Local return variable. */
lv_retval INTEGER := 0; -- Default value is 0.
/* Use a cursor, which will not raise an exception at runtime. */
find_lookup_id CURSOR
( cv_table_name VARCHAR
, cv_column_name VARCHAR
, cv_lookup_type VARCHAR ) FOR
SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = cv_table_name
AND common_lookup_column = cv_column_name
AND common_lookup_type = cv_type_name;
BEGIN
/* Assign a value when a row exists. */
FOR i IN get_lookup_value(pv_table_name, pv_column_name, pv_type_name) LOOP
lv_retval := i.common_lookup_id;
END LOOP;
/* Return 0 when no row found and the ID # when row found. */
RETURN lv_retval;
END;
$$ LANGUAGE plpgsql;
SELECT DISTINCT
CASE
WHEN NOT retval = 0 THEN retval
END AS "Return Lookup Value"
FROM (SELECT get_lookup_id('MEMBER', 'MEMBER_TYPE', cl.common_lookup_type) AS retval
FROM common_lookup cl) x
WHERE NOT retval = 0
DROP PROCEDURE IF EXISTS contact_insert
( IN pv_member_type VARCHAR(30)
, IN pv_account_number VARCHAR(10)
, IN pv_credit_card_number VARCHAR(19)
, IN pv_credit_card_type VARCHAR(30)
, IN pv_first_name VARCHAR(20)
, IN pv_middle_name VARCHAR(20)
, IN pv_last_name VARCHAR(20)
, IN pv_contact_type VARCHAR(30)
, IN pv_address_type VARCHAR(30)
, IN pv_city VARCHAR(30)
, IN pv_state_province VARCHAR(30)
, IN pv_postal_code VARCHAR(20)
, IN pv_street_address VARCHAR(30)
, IN pv_telephone_type VARCHAR(30)
, IN pv_country_code VARCHAR(3)
, IN pv_area_code VARCHAR(6)
, IN pv_telephone_number VARCHAR(10)
, IN pv_created_by INTEGER
, IN pv_last_updated_by INTEGER);
DROP PROCEDURE IF EXISTS contact_insert
( IN pv_member_type VARCHAR(30)
, IN pv_account_number VARCHAR(10)
, IN pv_credit_card_number VARCHAR(19)
, IN pv_credit_card_type VARCHAR(30)
, IN pv_first_name VARCHAR(20)
, IN pv_middle_name VARCHAR(20)
, IN pv_last_name VARCHAR(20)
, IN pv_contact_type VARCHAR(30)
, IN pv_address_type VARCHAR(30)
, IN pv_city VARCHAR(30)
, IN pv_state_province VARCHAR(30)
, IN pv_postal_code VARCHAR(20)
, IN pv_street_address VARCHAR(30)
, IN pv_telephone_type VARCHAR(30)
, IN pv_country_code VARCHAR(3)
, IN pv_area_code VARCHAR(6)
, IN pv_telephone_number VARCHAR(10)
, IN pv_user_name VARCHAR(20));
CREATE OR REPLACE PROCEDURE contact_insert
( IN pv_member_type VARCHAR(30)
, IN pv_account_number VARCHAR(10)
, IN pv_credit_card_number VARCHAR(19)
, IN pv_credit_card_type VARCHAR(30)
, IN pv_first_name VARCHAR(20)
, IN pv_middle_name VARCHAR(20)
, IN pv_last_name VARCHAR(20)
, IN pv_contact_type VARCHAR(30)
, IN pv_address_type VARCHAR(30)
, IN pv_city VARCHAR(30)
, IN pv_state_province VARCHAR(30)
, IN pv_postal_code VARCHAR(20)
, IN pv_street_address VARCHAR(30)
, IN pv_telephone_type VARCHAR(30)
, IN pv_country_code VARCHAR(3)
, IN pv_area_code VARCHAR(6)
, IN pv_telephone_number VARCHAR(10)
, IN pv_user_name VARCHAR(20)) AS
$$
DECLARE
/* Declare a who-audit variables. */
lv_system_user_id INTEGER;
/* Declare type variables. */
lv_member_type INTEGER;
lv_credit_card_type INTEGER;
lv_contact_type INTEGER;
lv_address_type INTEGER;
lv_telephone_type INTEGER;
/* Local surrogate key variables. */
lv_member_id INTEGER;
lv_contact_id INTEGER;
lv_address_id INTEGER;
lv_street_address_id INTEGER;
/* Declare local variable. */
lv_middle_name VARCHAR(20);
/* Declare error handling variables. */
err_num TEXT;
err_msg INTEGER;
BEGIN
/* Assing a null value to an empty string. */
IF pv_middle_name IS NULL THEN
lv_middle_name = '';
END IF;
/*
* Call the get_system_user_id function to assign surrogate
* key value to local variable.
*/
lv_system_user_id := get_system_user_id(pv_user_name);
/*
* Replace the character type values with their appropriate
* common_lookup_id values by calling the get_lookup_id
* function.
*/
lv_member_type := get_lookup_id('MEMBER','MEMBER_TYPE',pv_member_type);
lv_credit_card_type := get_lookup_id;
lv_contact_type := get_lookup_id;
lv_address_type := get_lookup_id;
lv_telephone_type := get_lookup_id;
/*
* Check for existing member row. Assign value when one exists,
* and assign zero when no member row is found.
*/
lv_member_id := get_member_id(pv_account_number);
/*
* Insert into the member table when no row is found.
*
* Replace the two subqueries by calling the get_lookup_id
* function for either the pv_member_type or credit_card_type
* value and assign it to a local variable.
*/
INSERT INTO member
( member_type
, account_number
, credit_card_number
, credit_card_type
, created_by
, last_updated_by )
VALUES
((SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'MEMBER'
AND common_lookup_column = 'MEMBER_TYPE'
AND common_lookup_type = pv_member_type)
, pv_account_number
, pv_credit_card_number
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'MEMBER'
AND common_lookup_column = 'CREDIT_CARD_TYPE'
AND common_lookup_type = pv_credit_card_type)
, lv_system_user_id
, lv_system_user_id )
RETURNING member_id INTO lv_member_id;
/*
* Insert into the member table when no row is found.
*
* Replace the subquery by calling the get_lookup_id
* function for the pv_contact_type value and
* assign it to a local variable.
*/
INSERT INTO contact
( member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, last_updated_by )
VALUES
( lv_member_id
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'CONTACT'
AND common_lookup_column = 'CONTACT_TYPE'
AND common_lookup_type = pv_contact_type)
, pv_first_name
, pv_middle_name
, pv_last_name
, lv_system_user_id
, lv_system_user_id )
RETURNING contact_id INTO lv_contact_id;
/*
* Insert into the member table when no row is found.
*
* Replace the subquery by calling the get_lookup_id
* function for the pv_address_type value and
* assign it to a local variable.
*/
INSERT INTO address
( contact_id
, address_type
, city
, state_province
, postal_code
, created_by
, last_updated_by )
VALUES
( lv_contact_id
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'ADDRESS'
AND common_lookup_column = 'ADDRESS_TYPE'
AND common_lookup_type = pv_address_type)
, pv_city
, pv_state_province
, pv_postal_code
, lv_system_user_id
, lv_system_user_id )
RETURNING address_id INTO lv_address_id;
/*
* Insert into the member table when no row is found.
*/
INSERT INTO street_address
( address_id
, street_address
, created_by
, last_updated_by )
VALUES
( lv_address_id
, pv_street_address
, lv_system_user_id
, lv_system_user_id )
RETURNING street_address_id INTO lv_street_address_id;
/*
* Insert into the member table when no row is found.
*
* Replace the subquery by calling the get_lookup_id
* function for the pv_telephone_type value and
* assign it to a local variable.
*/
INSERT INTO telephone
( contact_id
, address_id
, telephone_type
, country_code
, area_code
, telephone_number
, created_by
, last_updated_by )
VALUES
( lv_contact_id
, lv_address_id
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'TELEPHONE'
AND common_lookup_column = 'TELEPHONE_TYPE'
AND common_lookup_type = pv_telephone_type)
, pv_country_code
, pv_area_code
, pv_telephone_number
, lv_system_user_id
, lv_system_user_id );
EXCEPTION
WHEN OTHERS THEN
err_num := SQLSTATE;
err_msg := SUBSTR(SQLERRM,1,100);
RAISE NOTICE 'Trapped Error: %', err_msg;
END
$$ LANGUAGE plpgsql;
ORDER BY 1; Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.
PostgreSQL is a open source relational database system and is also knows as Postgres.
CREATE command is used to create a table, schema or an index.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
ALTER command is used to add, modify or delete columns or constraints from the database table.
ALTER TABLE Table_name ADD column_name datatype;
TRUNCATE command is used to delete the data present in the table but this will not delete the table.
TRUNCATE table table_name;
DROP command is used to delete the table along with its data.
DROP TABLE table_name;
RENAME command is used to rename the table name.
ALTER TABLE table_name1 RENAME to new_table_name1;
INSERT Statement is used to insert new records into the database table.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Select statement is used to select data from database tables.
SELECT column1, column2, ...
FROM table_name;
UPDATE statement is used to modify the existing values of records present in the database table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE statement is used to delete the existing records present in the database table.
DELETE FROM table_name where condition;