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

Question

I need to add unique constraint to the urls column:

CREATE OR REPLACE FUNCTION myFunction()  RETURNS TABLE(urls  TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
--my code
BEGIN
END;

How to make urls column unique?

Answer 1

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?