CREATE OR REPLACE FUNCTION sr_GetContactIDsForDevice(customerid bytea, with_emails_only boolean,deviceId bytea,contactDisplay character varying)
RETURNS SETOF ContactPreviewForDevice AS
$BODY$
DECLARE
r ContactPreviewForDevice;
BEGIN IF $2 = true THEN -- Only find contacts that have an email address
FOR r IN
SELECT DISTINCT c.contact_guid, c.cached_display, c.device_guid
FROM
spareroom_contact_customer cc
INNER JOIN spareroom_contact c ON cc.contact_guid = c.contact_guid
INNER JOIN spareroom_emailaddress e ON cc.contact_guid = e.contact_guid WHERE
cc.customer_guid = $1
AND c.device_guid = $3
AND c.cached_display like '%' || $4 || '%'
AND
NOT e.address = '' -- Apparently some contacts have empty email addresses.
ORDER BY c.cached_display
LOOP
RETURN NEXT r;
END LOOP;
RETURN; ELSE -- Find all contacts that belong to this customer's device
FOR r IN
SELECT DISTINCT c.contact_guid, c.cached_display, c.device_guid
FROM
spareroom_contact_customer cc INNER JOIN spareroom_contact c ON cc.contact_guid = c.contact_guid
WHERE cc.customer_guid = $1
AND c.device_guid = $3
AND c.cached_display like '%' || $4 || '%'
ORDER BY c.cached_display
LOOP
RETURN NEXT r;
END LOOP;
RETURN; END IF;END;
$BODY$-------------
这里有段PostgreSql的函数,我想在deviceId为空的时候查询所有device的信息,而不是单个deviceId的信息.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货