dpaste/Uxxx7 (SQL)

drop function if exists search_gids(text);
create function search_gids(term text) returns table(ItemType text, GID int, Label text, Placement2 text, Provisioner text, Config text)
language plpgsql
as $$
begin
term := '%' || lower(term) || '%';
return query
Select
'Category1' as ItemType, Category1.GID as GID, Category1.Placement1 as Label,
GID.Placement2 as Placement2, Vendor1.Provisioner as Provisioner, Vendor2.Config as Config
From Category1
JOIN GID ON GID.GID = Category1.GID
LEFT OUTER JOIN Vendor1 ON Vendor1.ID = Category1.Provisioner
LEFT OUTER JOIN Vendor2 ON Vendor2.ID = Category1.Config
WHERE lower(Category1.Placement1) LIKE term OR lower(GID.Placement2) LIKE term
union
Select
'Category2' as ItemType, Category2.GID as GID, Category2.Placement1 as Label,
GID.Placement2 as Placement2, Category2Provisioner.Category2Provisioner as Provisioner,
Category2Config.Config as Config
From Category2
JOIN GID ON GID.GID = Category2.GID
LEFT OUTER JOIN Category2Provisioner ON Category2Provisioner.ID = Category2.Provisioner
LEFT OUTER JOIN Category2Config ON Category2Config.ID = Category2.Config
WHERE lower(Category2.Placement1) LIKE term OR lower(GID.Placement2) LIKE term
union
Select
'Category3' as ItemType, Category3.GID as GID, Category3.Placement1 as Label,
GID.Placement2 as Placement2, Category3Provisioner.Provisioner as Provisioner,
Category3Config.Config as Config
From Category3
JOIN GID ON GID.GID = Category3.GID
LEFT OUTER JOIN Category3Provisioner ON Category3Provisioner.ID = Category3.Provisioner
LEFT OUTER JOIN Category3Config ON Category3Config.ID = Category3.Config
WHERE lower(Category3.Placement1) LIKE term OR lower(GID.Placement2) LIKE term
union
Select
'Category4' as ItemType, Category4.GID as GID, Category4.Placement1 as Label,
GID.Placement2 as Placement2, Category4Provisioner.Provisioner as Provisioner,
Category4.Config as Config
From Category4
JOIN GID ON GID.GID = Category4.GID
LEFT OUTER JOIN Category4Provisioner ON Category4Provisioner.ID = Category4.Provisioner
WHERE lower(Category4.Placement1) LIKE term OR lower(GID.Placement2) LIKE term;
end; $$

Edit this Snippet

Syntax

Expires

Right-to-Left

Content drop function if exists search_gids(text); create function search_gids(term text) returns table(ItemType text, GID int, Label text, Placement2 text, Provisioner text, Config text) language plpgsql as $$ begin term := '%' || lower(term) || '%'; return query Select 'Category1' as ItemType, Category1.GID as GID, Category1.Placement1 as Label, GID.Placement2 as Placement2, Vendor1.Provisioner as Provisioner, Vendor2.Config as Config From Category1 JOIN GID ON GID.GID = Category1.GID LEFT OUTER JOIN Vendor1 ON Vendor1.ID = Category1.Provisioner LEFT OUTER JOIN Vendor2 ON Vendor2.ID = Category1.Config WHERE lower(Category1.Placement1) LIKE term OR lower(GID.Placement2) LIKE term union Select 'Category2' as ItemType, Category2.GID as GID, Category2.Placement1 as Label, GID.Placement2 as Placement2, Category2Provisioner.Category2Provisioner as Provisioner, Category2Config.Config as Config From Category2 JOIN GID ON GID.GID = Category2.GID LEFT OUTER JOIN Category2Provisioner ON Category2Provisioner.ID = Category2.Provisioner LEFT OUTER JOIN Category2Config ON Category2Config.ID = Category2.Config WHERE lower(Category2.Placement1) LIKE term OR lower(GID.Placement2) LIKE term union Select 'Category3' as ItemType, Category3.GID as GID, Category3.Placement1 as Label, GID.Placement2 as Placement2, Category3Provisioner.Provisioner as Provisioner, Category3Config.Config as Config From Category3 JOIN GID ON GID.GID = Category3.GID LEFT OUTER JOIN Category3Provisioner ON Category3Provisioner.ID = Category3.Provisioner LEFT OUTER JOIN Category3Config ON Category3Config.ID = Category3.Config WHERE lower(Category3.Placement1) LIKE term OR lower(GID.Placement2) LIKE term union Select 'Category4' as ItemType, Category4.GID as GID, Category4.Placement1 as Label, GID.Placement2 as Placement2, Category4Provisioner.Provisioner as Provisioner, Category4.Config as Config From Category4 JOIN GID ON GID.GID = Category4.GID LEFT OUTER JOIN Category4Provisioner ON Category4Provisioner.ID = Category4.Provisioner WHERE lower(Category4.Placement1) LIKE term OR lower(GID.Placement2) LIKE term; end; $$