Table Variable and Table-Valued Function equivalent in PostgreSQL

Question

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?

I currently do this in Microsoft SQL server using Table Variables, below is a 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, San

JGH · Accepted Answer · 2020-01-16 23:48:06Z

1

You can use RETURN QUERY to add the result of various queries to the output.

CREATE OR REPLACE FUNCTION public.testf() 
RETURNS TABLE(id INTEGER, name text)
STABLE  
AS $$
  DECLARE

  BEGIN
    RETURN QUERY select 1 as id, 'abc' as name;

    RETURN QUERY select 2 as id, 'def' as name;

    RETURN QUERY select 3 as id, 'xyz' as name;

    -- Final return as set is now complete.
    return;

  END;
$$ LANGUAGE plpgsql;


select * from public.testf();

 id | name
----+------
  1 | abc
  2 | def
  3 | xyz
(3 rows)

answered Jan 16, 2020 at 23:48

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Table Variable and Table-Valued Function equivalent in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related