Skip to content

Instantly share code, notes, and snippets.

@jsgv
Created May 23, 2016 18:05
Show Gist options
  • Save jsgv/8d83ee9647dc6efe6ae88d81ca8f6d6a to your computer and use it in GitHub Desktop.
Save jsgv/8d83ee9647dc6efe6ae88d81ca8f6d6a to your computer and use it in GitHub Desktop.
Postgres function using returns
CREATE OR REPLACE FUNCTION public.fn_subscriber_save(
IN _emailaddress character varying,
IN _firstname character varying,
IN _lastname character varying,
IN _subscribeurl character varying,
IN _mobilenumber character varying DEFAULT NULL::character varying,
IN _ipaddress cidr DEFAULT NULL::cidr)
RETURNS TABLE(subscriberid integer) AS
$BODY$
DECLARE
__email_emailid integer;
__identity_emailid integer;
__identity_subscriberid integer;
BEGIN
__identity_subscriberid = 0;
-- lets see if there is already an email in database
SELECT emailid INTO __email_emailid
FROM email
WHERE emailaddress = _emailaddress;
-- insert email if it does not already exists
-- else update subscriber info
IF __email_emailid IS NULL THEN
INSERT INTO email(emailaddress)
VALUES(_emailaddress)
RETURNING emailid INTO __identity_emailid;
INSERT INTO subscriber(emailid, firstname, lastname, subscribeurl, ipaddress, mobilenumber)
VALUES(__identity_emailid, _firstname, _lastname, _subscribeurl, _ipaddress, _mobilenumber)
RETURNING subscriber.subscriberid INTO __identity_subscriberid;
ELSE
IF _mobilenumber IS NOT NULL THEN
UPDATE subscriber SET
mobilenumber = _mobilenumber,
dateupdated = (now() at time zone 'utc')
WHERE emailid = __email_emailid;
END IF;
IF _firstname <> '' THEN
UPDATE subscriber SET
firstname = _firstname,
dateupdated = (now() at time zone 'utc')
WHERE emailid = __email_emailid;
END IF;
IF _lastname <> '' THEN
UPDATE subscriber SET
lastname = _lastname,
dateupdated = (now() at time zone 'utc')
WHERE emailid = __email_emailid;
END IF;
SELECT s.subscriberid INTO __identity_subscriberid
FROM subscriber s
JOIN email e on e.emailid=s.emailid
WHERE e.emailaddress=_emailaddress;
END IF;
RETURN QUERY SELECT __identity_subscriberid as subscriberid;
END
$BODY$
LANGUAGE plpgsql VOLATILE
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment