How to add UNIQUE constraint to return column of a function?

I want get results of other table with batch queries not at once and return unique results

It's not possible to add a UNIQUE constraint to the return type of a function like you can do it in a table definition.

You could use UNION (not UNION ALL) to get unique URLs like this:

CREATE OR REPLACE FUNCTION myfunction() RETURNS TABLE(url TEXT) AS -- singular term "url" makes more sense $func$ BEGIN RETURN QUERY SELECT url FROM ... UNION SELECT url FROM ... -- more? ; END $func$ LANGUAGE plpgsql; 

This is an SQL feature and you wouldn't need PL/pgSQL for the single UNION query. Nor even a function. A plain (sub-)query or a VIEW are alternatives.

Related:

• PostgreSQL combine multiple select statements
• Combine two SELECT queries in PostgreSQL
• Function to loop through and select data from multiple tables

Or you just return everything from the function:

... RETURN QUERY SELECT url FROM ...; RETURN QUERY SELECT url FROM ...; -- more ? ... 

and de-duplicate the result in the call like:

SELECT DISTINCT url FROM myfunction(); 

To resolve the issue can I create temporary table?

You can. But be aware ...

• that's more expensive.
• if you define the column UNIQUE, an exception is raised and the whole transaction is rolled back if the constraint is violated.

• to avoid raising an exception use INSERT .. ON CONFLICT (url) DO NOTHING. More:

  • Can INSERT [...] ON CONFLICT be used for foreign key violations?