Hi,
I need to create a function in PostgreSQL for the following :
- Query multiple tables based on a business logic (all result sets return the same type of data)
- Compile all result sets into one table and return that table
Is it possible to accomplish this without using the temp tables in PostgreSQL?
Below is how I currently do it in Microsoft SQL server.
Sample function:
---------------------------------------------------
create FUNCTION test(@search_in nvarchar(500))
RETURNS @data_table TABLE
( item_id int, item_type nvarchar(1), first_name nvarchar(100), last_name nvarchar(100))
AS
BEGIN
-- from first table
if charindex('search_in_authors', @search_in) > 0
insert into @data_table
select item_id, 'a', first_name, last_name
from authors
where first_name = 'james'
-- from second table
if charindex('search_in_editors', @search_in) > 0
insert into @data_table
select item_id, 'e', first_name, last_name
from editors
where first_name = 'james'
-- from third table
if charindex('search_in_publishers', @search_in) > 0
insert into @data_table
select item_id, 'p', first_name, last_name
from publishes
where first_name = 'james'
-- there could be more like these based on the business logic...
(...)
-- finally return the records compiled in @data_table
RETURN
END
-----------------------------------------------------
Sample calls to the function:
// select * from dbo. test ('search_in_authors')
// select * from dbo. test ('search_in_authors, search_in_editors')
// select * from dbo. test ('search_in_authors, search_in_editors,search_in_publishers ')
-----------------------------------------------------
Are there any options in PostgreSQL to achieve this other than using a temp table ?
Thanks,
Santosh