Compile query results into a table

[Santosh Udupi <email@xxxxxxxxx>]

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