CREATE OR REPLACE FUNCTION public.vls_function_update_account_profile_user_password(
	_userid integer,
	_user_password character varying,
	_vls_personid uuid)
RETURNS TABLE(
	_vls_user_id integer, 
	_firstname character varying, 
	_lastname character varying,
	_mobile character varying, 
	_default_role_id integer, 
	_email character varying, 
	_password character varying, 
	_user_uuid uuid, 
	_vwp_person_id uuid, 
	_status integer, 
	_vls_person_id uuid
) 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
BEGIN 
	-- Select * From vls_function_update_account_profile_user_password(2, 'Apple#123', '198d4c52-e25b-4c5d-bdec-8775bbc7bac5')
	-- Select * From vls_users where vls_user_id=2
	
	IF _vls_personid IS NULL THEN 
		RETURN QUERY (
			SELECT 
				vls_users.vls_user_id, 
				vls_users.firstname, 
				vls_users.lastname, 
				vls_users.mobile,
				vls_users.default_role_id,
				vls_users.email_id, 
				vls_users.password, 
				vls_users.user_uuid,
				vls_vwp_org_person_xref.vwp_person_id,
				vls_users.status,
				vls_users.vls_platform_user_uuid
			FROM 
				vls_users
			LEFT JOIN 
				vls_vwp_org_person_xref 
			ON 
				vls_users.vls_platform_user_uuid = vls_vwp_org_person_xref.vls_person_id
			WHERE 
				vls_users.default_role_id = 5
		);
	ELSIF _vls_personid = (SELECT vls_platform_user_uuid FROM vls_users WHERE vls_platform_user_uuid = _vls_personid) THEN 
		UPDATE vls_users SET password = _user_password WHERE vls_platform_user_uuid = _vls_personid;
		UPDATE vls_platform_users SET password = _user_password WHERE platform_user_id = _vls_personid;

		RETURN QUERY (
			SELECT 
				vls_users.vls_user_id, 
				vls_users.firstname, 
				vls_users.lastname, 
				vls_users.mobile,
				vls_users.default_role_id,
				vls_users.email_id, 
				vls_users.password, 
				vls_users.user_uuid,
				vls_vwp_org_person_xref.vwp_person_id,
				vls_users.status,
				vls_users.vls_platform_user_uuid
			FROM 
				vls_users
			LEFT JOIN 
				vls_vwp_org_person_xref 
			ON 
				vls_users.vls_platform_user_uuid = vls_vwp_org_person_xref.vls_person_id
			WHERE 
				vls_users.vls_platform_user_uuid = _vls_personid
		);
	ELSE
		RETURN QUERY (
			SELECT 
				0,
				'Invalid UserID'::varchar,
				'Invalid UserID'::varchar,
				'Invalid UserID'::varchar,
				0,
				'Invalid UserID'::varchar,
				'Invalid UserID'::varchar,
				NULL::uuid,
				NULL::uuid,
				0,
				NULL::uuid
		);
	END IF;
END;
$BODY$;

PostgreSQL online editor

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.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

  • Postgres is not only free and open-source but also it is highly extensible.
  • Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
  • ACID(Atomicity, Consistency, Isolation, Durability) compliant.
  • First DBMS which implemented Multi-version concurrency control (MVCC) feature.
  • It's the default database server for MacOS.
  • It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;